qlikviewqliksenseqlik-expression

Qlik Sense Script sekecting data based of a specific criteria

Sorry if I am missing something obvious as I am trying to learn Qlik Sense.

I have a table with about 1 mil rows, I want to filter the data based the criteria below:

  1. Find the 1st Lost Charges for the serial
  2. Now switch the search to the serial + suffix where the Lost Charge was found and look for Lost Credits:
    • a. If Lost Credits found - Ignore the data and continue to the next Lost Charges
    • b. If Lost Credits not Found - Select all the data for that serial (entire serial, not just the suffix where Lost Charges was found) starting from the Lost Charges (Lost Charges not included)
      1. Repeat for each serial.

Example Data:

Serial Sfx Ser|Sfx Value Charge Date Charge Type 96 1 96|1 3.50 30/09/2002 Rental Charges 96 1 96|1 3.50 31/10/2002 Rental Charges 96 1 96|1 3.50 30/11/2002 Rental Charges 96 1 96|1 3.50 31/12/2002 Rental Charges 96 1 96|1 3.50 31/01/2003 Rental Charges 96 1 96|1 3.50 28/02/2003 Rental Charges 96 1 96|1 3.50 31/03/2003 Rental Charges 96 1 96|1 3.50 30/04/2003 Rental Charges 96 1 96|1 3.50 31/05/2003 Rental Charges 96 1 96|1 3.50 30/06/2003 Rental Charges 96 1 96|1 3.50 31/07/2003 Rental Charges 96 1 96|1 3.50 31/08/2003 Rental Charges 96 1 96|1 112.50 14/10/2003 Lost Charges 96 2 96|2 3.50 30/11/2003 Rental Charges 96 2 96|2 3.50 31/12/2003 Rental Charges 96 2 96|2 3.50 31/01/2004 Rental Charges 96 3 96|3 3.50 31/08/2005 Rental Charges 96 3 96|3 3.50 30/09/2005 Rental Charges 96 3 96|3 3.50 31/10/2005 Rental Charges 96 4 96|4 3.50 31/01/2006 Rental Charges 96 4 96|4 3.50 28/02/2006 Rental Charges 96 4 96|4 112.50 10/05/2006 Lost Charges 96 4 96|4 -112.50 15/05/2006 Lost Credits

Resulting data should be:

Serial Sfx Ser|Sfx Value Charge Date Charge Type 96 2 96|2 3.50 30/11/2003 Rental Charges 96 2 96|2 3.50 31/12/2003 Rental Charges 96 2 96|2 3.50 31/01/2004 Rental Charges 96 3 96|3 3.50 31/08/2005 Rental Charges 96 3 96|3 3.50 30/09/2005 Rental Charges 96 3 96|3 3.50 31/10/2005 Rental Charges 96 4 96|4 3.50 31/01/2006 Rental Charges 96 4 96|4 3.50 28/02/2006 Rental Charges 96 4 96|4 112.50 10/05/2006 Lost Charges 96 4 96|4 -112.50 15/05/2006 Lost Credits

I tried to do with just the set analysis, but couldn't get the desired results.

I have loaded the data and created a 2nd table to filter some the data that is pre 1st Lost Charges as per below:

ChargeData:
LOAD
    Serial_KEY,
    "Serial number true" as SerNo,
    "Suffix number" as Sfx,
    Value,
    "Charge Date",
    "Charge Type",
    "Additional Text",
    Customer,
    "Invoice Document",
    Currency,
    "Charge Type" &'|'& Date([Charge Date]) as Charge_KEY
FROM [Transform.qvd]
(qvd);


LostCylinders:
Load
    SerNo,
    Concat(IF([Charge Type]='Lost Charges','L',
            IF([Charge Type]='Lost Credits','C',Null()))) as LostFlag
Resident ChargeData
Group by SerNo
;

Then in the app a measure that sum all of the Lost Charges per Serial

sum({$<"Charge Type"={"Lost Charges"}>} Value )

But I am not sure how to make it only sum values after the 1st Lost Charges.

Solution

  • Thanks to The Budac I was able to achieve the desired results.

    I based my code on his answer, with a few additions/changes.

    1st I loaded all the data and added a Credit Flag via mapping for rows where Charge Type is "Lost Credits" (used later)

    Map_Cred:
Mapping
Load
Serial_KEY,
'1' as [Lost Credit Flag]
FROM [lib://...qvd](qvd)
Where [Charge Type]='Lost Credits'
;

Raw_Data:
LOAD
*,
applymap('Map_Cred',Serial_KEY,' ') as [Cred Flag]
FROM [lib://...qvd](qvd)
;

    Then I created a map, as suggested, with the addition of the Credit Flag, this is required to eliminate partial credits (IE where Lost Credits <> Lost Charges)

    Map_Lost:
Mapping
load
SerNo,
Date(Min([First Lost])) as [First Lost Date]
where [Lost Total]<>0
Group by SerNo
;

Load 
SerNo,
Sfx,
Sum(Value) as [Lost Total],
date(min([Charge Date])) as [First Lost]
Resident Raw_Data
Where [Cred Flag]<>1 and
Match([Charge Type],'Lost Credits','Lost Charges')
group by SerNo,Sfx
;

    Then applied the above mapping to the main data

    CD1:
Load
SerNo,
Sfx,
Serial_KEY,
Value,
[Charge Date],
[Charge Type],
ApplyMap('Map_Lost',SerNo,'12/12/2025') as [First Lost Date],
if(ApplyMap('Map_Lost',SerNo,'12/12/2025')<[Charge Date],'After','Before') as Before_After 
Resident Raw_Data
;

Drop table Raw_Data
;

    And Finally I was able to breakdown the charge totals in to separate columns with set analysis (replacing Charge Type per column).

    sum({<Before_After={'After'},"Charge Type"={"Lost Charges"}>} Value)