Showing flag for the max period

[deep2021]

Hi All,

I have sample data as per the below,

OBJECT_TYPE	OBJECT_UID	PERIOD
A	8085	2003Q4
A	8085	2004Q1
A	8085	2004Q2
A	8085	2004Q3
A	8086	2004Q4
A	8086	2005Q1
A	8086	2005Q2
A	8086	2005Q3

Could you please suggest how to create the flag for the max period  and how to show max period  for OBJECT_UID from the above table.

The newly created flag could be as per the below

OBJECT_TYPE	OBJECT_UID	PERIOD	Flag
A	8085	2003Q4	-
A	8085	2004Q1	-
A	8085	2004Q2	-
A	8085	2004Q3	1
A	8086	2004Q4	-
A	8086	2005Q1	-
A	8086	2005Q2	-
A	8086	2005Q3	1

thanks

[stevejoyce]

You can get away with finding period by keeping PERIOD as a string and using maxstring().

Do you want the max Period for each Object Type / Object UID?  Or for the entire data set?

This would add a flag to your data model, and then your expressions' set analysis can have fl_max_period = {1}.  If you want more granular max periods (by object) you can add the fields to a group by to the maxstring statement.

data:
LOAD
*

FROM https://community.qlik.com/t5/New-to-Qlik-Sense/Showing-flag-for-the-max-period/td-p/1832561
//https://community.qlik.com/t5/New-to-Qlik-Sense/Distribute-value-in-Pie-Chart/td-p/1832164
//https://community.qlik.com/t5/QlikView-App-Dev/Merging-Year-from-column-into-X-Axis-with-Months/td-p...
(html, utf8, embedded labels, table is @1)
;

left join (data)
load
maxstring(PERIOD) as PERIOD
,1 as fl_max_period
resident data;

another option if you need depending on what you want to do, is set a variable in your load script (this makes most sense if you want it to be a global max period).

data:
LOAD
*

FROM https://community.qlik.com/t5/New-to-Qlik-Sense/Showing-flag-for-the-max-period/td-p/1832561
//https://community.qlik.com/t5/New-to-Qlik-Sense/Distribute-value-in-Pie-Chart/td-p/1832164
//https://community.qlik.com/t5/QlikView-App-Dev/Merging-Year-from-column-into-X-Axis-with-Months/td-p...
(html, utf8, embedded labels, table is @1)
;

fl_max_period:
load
maxstring(PERIOD) as fl_max_period
resident data;

let vMaxPeriod = peek('fl_max_period', 0, 'max_period');

drop table fl_max_period;

Then your front-end calculations can have set anlalysis like PERIOD = {'$(vMaxPeriod)'} 

[deep2021]

Thanks for you valuable response.

We need max period as per OBJECT_UID.

There would be a group by clause for OBJECT_UID.

So that created flags would be,

OBJECT_TYPE	OBJECT_UID	PERIOD	Flag
A	8085	2003Q4	-
A	8085	2004Q1	-
A	8085	2004Q2	-
A	8085	2004Q3	1
A	8086	2004Q4	-
A	8086	2005Q1	-
A	8086	2005Q2	-
A	8086	2005Q3	1

could you suggest on in the above scenario how it would works.

[Kushal_Chawda · Accepted solution]

@deep2021  may be you could try below

Data:
LOAD OBJECT_TYPE,
            OBJECT_UID,
            PERIOD
FROM Table;

left join(Data)
LOAD 
            OBJECT_UID ,
            maxstring(PERIOD) as PERIOD,
            1 as Flag
resident Data
group by  OBJECT_UID;

If you want your flag and max period just by OBJECT_UID & OBJECT_TYPE then just include OBJECT_TYPE in group by and load statement on resident load