Create a flag column based on NUMBER

[sunilkumarqv]

HI all ,

Please help me to create a FLAG column  below table where for each NUMBER to get Last flag as 1 else 0

NUMBER	DATE	STATUS	FLAG
1	29/12/2015 :	Assigned	0
1	29/12/2015 :	Assigned	0
1	29/12/2015 :	Resolved	1
2	15/02/2016 :	Assigned	0
2	15/02/2016 :	In Progress	1
3	04/02/2016 :	Pending	0
3	04/02/2016 :	Assigned	0
3	04/02/2016 :	In Progress	0
3	04/02/2016 :	Resolved	1
4	25/02/2016 :	Pending	0
4	25/02/2016 :	Assigned	0
4	25/02/2016 :	In Progress	0
4	25/02/2016 :	Resolved	1
5	26/02/2016 :	Assigned	0
5	26/02/2016 :	In Progress	1

[Kushal_Chawda]

Do you have Timestamp format of Date? If so it will be easy to flag the latest Status based on max(Date)

[sunilkumarqv]

yes have timestamp

can you plz share script hear .

[jagan · Accepted solution]

Hi,

Try like this

LOAD

*,

If(Peek('NUMBER') <> NUMBER, 1, 0) AS Flag

FROM DataSource;

Hope this helps you.

Regards,

Jagan.

[Kushal_Chawda]

make sure that You Date is in proper Timestamp format

Data:

LOAD Number,

           num(Date) as Date,

          Status

From Table;

left join(Data)

LOAD  Number,

            max(Date) as Date

           1 as MaxDateFlag

Resident Data

group by Number;

Final:

noconcatenate

LOAD *,

if(len(trim(MaxDateFlag))<=0,0,MaxDateFlag) as Flag

resident Data;

drop table Data;

[siddharthsoam]

You can simply use maxstring(date)..