topic To create flag New ID or Old ID based on CreateDate in QlikView

To create flag New ID or Old ID based on CreateDate

karan_kn — Wed, 25 Nov 2020 16:16:04 GMT

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
];

Re: To create flag New ID or Old ID based on CreateDate

sunny_talwar — Tue, 25 Jun 2019 11:56:07 GMT

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 ];

Re: To create flag New ID or Old ID based on CreateDate

karan_kn — Tue, 25 Jun 2019 18:29:38 GMT

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'

Re: To create flag New ID or Old ID based on CreateDate

sunny_talwar — Tue, 25 Jun 2019 19:42:06 GMT

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

Re: To create flag New ID or Old ID based on CreateDate

karan_kn — Wed, 26 Jun 2019 05:38:54 GMT

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'

Re: To create flag New ID or Old ID based on CreateDate

sunny_talwar — Wed, 26 Jun 2019 11:05:48 GMT

Something like this?

Script