Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
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
Partner
Partner

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;
Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

5 Replies
Vegar
Partner
Partner

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;
Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

tan_chungkam
Creator
Creator
Author

@Vegar ,

Thanks for the soluton.

It work perfect.

May i know whats those scripting doing?

Vegar
Partner
Partner

 

 

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 . 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
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
Partner
Partner

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. 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes