Qlik Sense Script sekecting data based of a specific criteria

Question

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:

Find the 1st Lost Charges for the serial
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:

Resulting data should be:

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.

Could you give us the code of what you have tried? – Snedecor May 06 '20 at 15:13 — Snedecor, May 06 '20 at 15:13

The Budac · Answer 1 · 2020-05-08T06:55:28.310

So you need to find the minimum per Serial.

(temp_CHARGES is just my version of the example data you provided)

You'll see in the next step why I use the mapping load instead of a join in the next step

MAP_SERIAL_FIRST_LOST_CHARGES:
mapping
load
   Serial,
   [First Lost Date]
where [Lost Total]<>0;  
load 
  Serial,
  Sfx,
  sum(Value) as [Lost Total],
date(min([Charge Date])) as [First Lost Date]
resident temp_CHARGES
where match([Charge Type],'Lost Charges','Lost Credits')
group by Serial,Sfx
;

Now I can use that mapped first date to test the rest. The aplymap function also allows for a default value so I provide a date far in the future '2025/12/12' to make my if() work

CHARGES_BASE:
load 
    Serial, 
    Sfx, 
    [Ser|Sfx], 
    Value, 
    [Charge Date], 
    [Charge Type],
    applymap('MAP_SERIAL_FIRST_LOST_CHARGES',Serial,'2025/12/12') as [First Lost Date],
    if(applymap('MAP_SERIAL_FIRST_LOST_CHARGES',Serial,'2025/12/12')<[Charge Date],'After First Lost','Before First Lost') as BEFORE_AFTER
Resident 
    temp_CHARGES
    ;

drop table temp_CHARGES
;

So now I have a data table like this

Then with some simple Set Analysis I can get this table in the front end

sum({<BEFORE_AFTER={'After First Lost'}>} Value)

Hello, this is very useful and works well when the data is arranged as in my example, but if I have a case where Serial has a Lost Charges followed by Lost Credits within the same suffix, the mapping still treats that Lost Charges as the 1st Lost Charges, where as it should be ignored because it has Lost Credits. `Serial / Sfx / Type 96 / 1 / Lost Charge 96 / 1 / Lost Credits 96 / 2 / Lost Charges <- should be the 1st Lost Charges 96 / 3 / Rental Charges` — DDoze, May 07 '20 at 10:19
I've edited my answer to include checking only for non-zero Lost sfxs — The Budac, May 08 '20 at 06:51

score 1 · Accepted Answer · edited May 14 '20 at 19:38

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)

Qlik Sense Script sekecting data based of a specific criteria

2 Answers2