Set Flag

[Anonymous]

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

[settu_periasamy]

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;

[sunny_talwar]

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;