Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tan_chungkam
Creator
Creator

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.T1T1T2T2

1 Solution

Accepted Solutions
Vegar
MVP
MVP

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;

View solution in original post

5 Replies
Vegar
MVP
MVP

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
Creator
Creator
Author

@Vegar ,

Thanks for the soluton.

It work perfect.

May i know whats those scripting doing?

Vegar
MVP
MVP

 

 

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
Creator
Creator
Author

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
MVP
MVP

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.