topic Re: Set Flag in QlikView

Set Flag

Anonymous — Wed, 11 May 2016 15:40:51 GMT

Hi All,

I Have a dataset as shown below

Date , Value

01/01/2016, 100

15/01/2016, 200

25,01/2016, 500

05/02/2016, 150

09/02/2016, 75

07/03/2016, 250

What is want is set Flag is equal to 1 if it is max date of that month

Expected o/p

Date , Value , Flag

01/01/2016, 100, 0

15/01/2016, 200, 0

25,01/2016, 500, 1

05/02/2016, 150, 0

09/02/2016, 75, 1

07/03/2016, 250, 1

Re: Set Flag

settu_periasamy — Wed, 11 May 2016 16:10:12 GMT

Try this..

SET DateFormat='D/M/YYYY';

T1:

LOAD *,Month(Date) as Month;

LOAD * Inline [

Date, Value

01/01/2016, 100

15/01/2016, 200

25/01/2016, 500

05/02/2016, 150

09/02/2016, 75

07/03/2016, 250

];

Left Join(T1)

LOAD Month,Max(Date) as Date,1 as Flag Resident T1 Group by Month;

NoConcatenate

Final:

LOAD Date,Month,Value,if(IsNull(Flag),0,Flag) as Flag Resident T1;

DROP Table T1;

Re: Set Flag

sunny_talwar — Wed, 11 May 2016 17:03:34 GMT

Another option:

T1:

LOAD *,Month(Date) as Month;

LOAD * Inline [

Date, Value

01/01/2016, 100

15/01/2016, 200

25/01/2016, 500

05/02/2016, 150

09/02/2016, 75

07/03/2016, 250

];

Final:

LOAD *,

If(Month = Peek(Month), 0, 1) as Flag

Resident T1

Order By Date desc;

DROP Table T1;