Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

tan_chungkam
Contributor

Load max period

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.image.pngT1image.pngT2

1 Solution

Accepted Solutions
Partner
Partner

Re: Load max period

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;
???
Celebrating my 300 accepted solution in the Qlik Community
???
Tags (2)
5 Replies
Partner
Partner

Re: Load max period

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;
???
Celebrating my 300 accepted solution in the Qlik Community
???
Tags (2)
tan_chungkam
Contributor

Re: Load max period

@Vegar ,

Thanks for the soluton.

It work perfect.

May i know whats those scripting doing?

Highlighted
Partner
Partner

Re: Load max period

 

 

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 . 

???
Celebrating my 300 accepted solution in the Qlik Community
???
tan_chungkam
Contributor

Re: Load max period

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?

Partner
Partner

Re: Load max period

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. 

???
Celebrating my 300 accepted solution in the Qlik Community
???