To create flag New ID or Old ID based on CreateDate

[karan_kn]

To create flag New ID or Old ID based on Date using PrimaryID in Load statement.

Create Flag:
-If Create Date for PrimaryID  >'31/10/2018' Flag 'New ID'
-If Create Date for PrimaryID  <=31/10/2018 Flag 'Old ID'

Table:
LOAD * INLINE [
CreateDate, PrimaryID, GroupID
5/4/2018, 1, 1001
1/1/2019, 2, 1001
1/20/2018, 3, 1002
7/11/2018, 4, 1002
1/24/2018, 5, 1003
7/19/2018, 6, 1002
7/3/2018, 7, 1002
3/31/2018, 8, 1002
4/11/2018, 9, 1003
8/28/2018, 10, 1001
11/25/2018, 11, 1005
9/11/2018, 12, 1005
10/4/2018, 13, 1001
3/25/2018, 14, 1005
2/4/2019, 15, 1008
1/18/2019, 16, 1002
4/1/2018, 17, 1006
10/19/2018, 18, 1008
2/22/2019, 19, 1006
4/18/2018, 20, 1007
];

[sunny_talwar · Accepted solution]

Are you looking for this?

Table:
LOAD *,
	 If(CreateDate > MakeDate(2018, 10, 31), 'New ID', 'Old ID') as CreateFlag;
LOAD * INLINE [
    CreateDate, PrimaryID, GroupID
    5/4/2018, 1, 1001
    1/1/2019, 2, 1001
    1/20/2018, 3, 1002
    7/11/2018, 4, 1002
    1/24/2018, 5, 1003
    7/19/2018, 6, 1002
    7/3/2018, 7, 1002
    3/31/2018, 8, 1002
    4/11/2018, 9, 1003
    8/28/2018, 10, 1001
    11/25/2018, 11, 1005
    9/11/2018, 12, 1005
    10/4/2018, 13, 1001
    3/25/2018, 14, 1005
    2/4/2019, 15, 1008
    1/18/2019, 16, 1002
    4/1/2018, 17, 1006
    10/19/2018, 18, 1008
    2/22/2019, 19, 1006
    4/18/2018, 20, 1007
];

[karan_kn]

Thanks Sunny, worked perfectly.

Is it possible to do same for Group ID?  

Create Flag: 
Flag1
-If Create Date for PrimaryID  >'31/10/2018' Flag 'New ID'
-If Create Date for PrimaryID  <=31/10/2018 Flag 'Old ID'

and 

Flag2
-If Create Date for GroupID  >'31/10/2018' Flag 'New GroupID'
-If Create Date for GroupID  <=31/10/2018 Flag 'Old GroupID'

[sunny_talwar]

A group seem to have multiple create_date... which create date should we be comparing it to?

[karan_kn]

By latest create date 

-If any Create Date for GroupID  start from >'31/10/2018' Flag 'New GroupID'
-If any Create Date for GroupID  start from  <=31/10/2018 Flag 'Old GroupID'

[sunny_talwar]

Something like this?

Script

Table:
LOAD *,
	 If(CreateDate > MakeDate(2018, 10, 31), 'New ID', 'Old ID') as CreateFlag;
LOAD * INLINE [
    CreateDate, PrimaryID, GroupID
    5/4/2018, 1, 1001
    1/1/2019, 2, 1001
    1/20/2018, 3, 1002
    7/11/2018, 4, 1002
    1/24/2018, 5, 1003
    7/19/2018, 6, 1002
    7/3/2018, 7, 1002
    3/31/2018, 8, 1002
    4/11/2018, 9, 1003
    8/28/2018, 10, 1001
    11/25/2018, 11, 1005
    9/11/2018, 12, 1005
    10/4/2018, 13, 1001
    3/25/2018, 14, 1005
    2/4/2019, 15, 1008
    1/18/2019, 16, 1002
    4/1/2018, 17, 1006
    10/19/2018, 18, 1008
    2/22/2019, 19, 1006
    4/18/2018, 20, 1007
];

Left Join (Table)
LOAD GroupID,
	 Max(CreateDate) as LatestCreateDate,
	 If(Max(CreateDate) > MakeDate(2018, 10, 31), 'New ID', 'Old ID') as GroupCreateFlag
Resident Table
Group By GroupID;