Load max period

[tan_chungkam]

Hi lovely qlikers,

I would like to always load the max period in my T2 scripting.

T1 is to load all periods files.

T2 is to load max period file.

any idea to do it? 

Please find below my screenshot.T1T2

[Vegar · Accepted solution]

Insert the following script right before dropping the T1 table.

TmpMaxPeriod:
LOAD Max(_period) as MaxPeriod;
LOAD FieldValue('Period', recno()) as _period
AUTOGENERATE FieldValueCount('Period');

Then replace your T2 where statement to this

WHERE Exists(MaxPeriod, Period);

Then in the end you can drop the TmpMaxPeriod table

DROP TABLE TmpMaxPeriod;

[tan_chungkam]

@Vegar ,

Thanks for the soluton.

It work perfect.

May i know whats those scripting doing?

[Vegar]

TmpMaxPeriod:
LOAD Max(_period) as MaxPeriod;
LOAD FieldValue('Period', recno()) as _period
AUTOGENERATE FieldValueCount('Period');

The TmpMaxPeriod  reads all possible fieldvalues in the Period field and fetches the max value of Period into the field MaxPeriod.

WHERE Exists(MaxPeriod, Period);

Exists checks if the expression in second parameter (Period) matches any fieldvalue in the first parameter field (MaxPeriod). Hence the only MaxPeriod value is the latest Period you will only LOAD  the latest Period transactions.

You could solve this issue in multiple ways, but this is a quite fast and optimized method. Part one is considered one of the fastest ways to fetch max values from an resident table and part two will not break optimized load from QVD files as an   logical < > or =  statement would . 

[tan_chungkam]

Hi @Vegar ,

Once again thanks for your quick response.

I have another question.

What if i would like load max year and max period at the same time?

Do i use the similar scripting?

[Vegar]

I would probably tried to create a field containing YearPeriod to  make sure you get the correct max and then fetched the year part and period part from that field after I've identified the max YearMonth.