Identifying Inventory Levels by Latest Date in Qlik

[SonPhanHumanIT]

Hello Qlik experts,

I'm facing a small issue with Set Analysis and have put together a demo set of data for you:

Load * Inline [
Part, Date, Quantity
207925, 05.02.2024,1
207925, 06.02.2024,-1
207925, 12.02.2024,
207925, 14.02.2024,5
207925, 15.02.2024,-5
42172, 09.02.2024,101
42172, 10.02.2024,
42172, 11.02.2024,
42172, 12.02.2024,
42172, 13.02.2024,

];

I'm interested in all articles that, grouped by article, have inventory on the latest date. Specifically, in this example, I'm interested in examining the two highlighted rows. Do you have any ideas on how to solve this? This simplified example consists of a single table, whereas in reality, the data is split across multiple tables.

Thank you!

Best regards Son

[anat]

Table1:

Load * from table;

Right Join 

LOAD partid, 

          Max(Date) as Date

Resident Table1

Group By partyid;

[stevedark · Accepted solution]

Hi @SonPhanHumanIT 

It appears you are not actually looking for the latest date for each part, but the latest date with a quantity for each part?

The best way is almost always to do more of the work in the load script. This script will make it work for the INLINE load you have, you may have to adapt it for real data:

// Load the data with a key field for the join
SrcData:
LOAD
    Part&Date(Date, 'YYYYMMDD') as PartDateKey,
    *
    ;
LOAD
    Part,
    Date(Date#(Date, 'DD.MM.YYYY')) as Date,
    Quantity
Inline [
Part, Date, Quantity
207925, 05.02.2024,1
207925, 06.02.2024,-1
207925, 12.02.2024,
207925, 14.02.2024,5
207925, 15.02.2024,-5
42172, 09.02.2024,101
42172, 10.02.2024,
42172, 11.02.2024,
42172, 12.02.2024,
42172, 13.02.2024,
];

// Join a flag to flag the latest
LEFT JOIN (SrcData)
LOAD
    maxstring(PartDateKey) as PartDateKey,
    1 as IsLatest
RESIDENT SrcData
WHERE alt(Quantity, 0) <> 0
GROUP BY Part;

The date# is required because any dates from an inline could be interpreted as strings, and will therefore not sort correctly.

The composite key in the preceding load is not strictly necessary, but I like to create unique keys for each table anyway (unless data volumes and available memory make this a bad idea).

Once the data are loaded and the key is created the subsequent grouped load with a join attaches a binary flag to each of the rows that is the latest row for the part with a value.

This flag can then be used in set analysis: sum({<IsLatest*={1}>}Quantity)

Hope that helps,

Steve

[SonPhanHumanIT]

Yes, I have also coded that in the script before. I was just wondering if it would be possible to solve the problem with Set Analysis. Thank you for your input