topic Re: Flagging by ID and Date field in QlikView

Flagging by ID and Date field

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

Can someone help me with this tricky task.

My task is flag the ID's with New or Repeat. In a month if the Primary ID have multiple Secondary ID.

Example:

We have to flag as 'New' / 'Repeat'. Earlier Date - 'New'

else

If It's same date, Earlier ID is 'New'

Here are my inline text, and screenshot for better understanding.

Set Flag like this:

LOAD * INLINE [

Primary_ID, Secondary_ID, Product Name, Date

1, A101, AAA, 2/8/2018

1, A102, BBB, 2/7/2018

2, A103, AAA, 2/21/2018

3, A104, BBB, 2/4/2018

3, A105, AAA, 2/7/2018

3, A106, BBB, 2/9/2018

4, A107, AAA, 2/10/2018

4, A108, BBB, 2/10/2018

5, A109, AAA, 2/15/2018

6, A110, BBB, 2/18/2018

6, A111, AAA, 2/18/2018

6, A112, BBB, 2/18/2018

7, A113, AAA, 2/3/2018

7, A114, BBB, 2/23/2018

];

Re: Flagging by ID and Date field

sunny_talwar — Tue, 06 Mar 2018 15:54:13 GMT

Try this

Table:

LOAD * INLINE [

Primary_ID, Secondary_ID, Product Name, Date

1, A101, AAA, 2/8/2018

1, A102, BBB, 2/7/2018

2, A103, AAA, 2/21/2018

3, A104, BBB, 2/4/2018

3, A105, AAA, 2/7/2018

3, A106, BBB, 2/9/2018

4, A107, AAA, 2/10/2018

4, A108, BBB, 2/10/2018

5, A109, AAA, 2/15/2018

6, A110, BBB, 2/18/2018

6, A111, AAA, 2/18/2018

6, A112, BBB, 2/18/2018

7, A113, AAA, 2/3/2018

7, A114, BBB, 2/23/2018

];

FinalTable:

LOAD *,

If(Primary_ID = Previous(Primary_ID), 'Repeat', 'New') as Flag

Resident Table

Order By Primary_ID, Date;

DROP Table Table;

Re: Flagging by ID and Date field

karan_kn — Tue, 06 Mar 2018 16:31:02 GMT

Thanks It's perfect

Re: Flagging by ID and Date field

karan_kn — Thu, 08 Mar 2018 13:09:35 GMT

Hi stalwar1 Sunny,

when I change the Prodname and IDs not working, please help me to fix.

Table:

LOAD * INLINE [

Primary_ID, Secondary_ID, Product Name, Date

1, A101, AeehjgrAA, 2/8/2018

1, A102, BBffggdfgB, 2/7/2018

2, A105, AAfgjhdfA, 2/21/2018

3, A104, BBfd6ghgB, 2/14/2018

3, A107, AfgdjjhfAA, 2/7/2018

3, A109, BBdfhgdfB, 2/9/2018

4, A111, AAdfgfgA, 2/10/2018

4, A114, BBdffhkgB, 2/10/2018

5, A116, AAdfdfgA, 2/15/2018

6, A120, BBdkfgB, 2/18/2018

6, A118, AAdjhkfgA, 2/18/2018

6, A122, BBdrefgB, 2/18/2018

7, A127, AAdjmfA, 2/23/2018

7, A132, BBfytudgB, 2/3/2018

8, A147, HHdbcfgH, 2/13/2018

8, A155, KKdfujgK, 2/17/2018

8, A169, LLdhhhfgL, 2/3/2018

9, A175, LLdfjkgL, 2/9/2018

9, A170, LLdffgL, 2/9/2018

];

FinalTable:

LOAD *,

If(Primary_ID <= Previous(Primary_ID), 'Repeat', 'New') as Flag

Resident Table

Order By Primary_ID, Date;

DROP Table Table;

Re: Flagging by ID and Date field

sunny_talwar — Thu, 08 Mar 2018 13:12:31 GMT

when I change the Prodname and IDs not working, please help me to fix.

Not sure what you mean Karan?

Re: Flagging by ID and Date field

karan_kn — Thu, 08 Mar 2018 13:19:09 GMT

Flag is not tagging correctly

We have to flag as 'New' / 'Repeat'.

Earlier Date - 'New'

else

If It's same date, Earlier ID is 'New'

Re: Flagging by ID and Date field

karan_kn — Thu, 08 Mar 2018 13:25:28 GMT

stalwar1‌

Re: Flagging by ID and Date field

sunny_talwar — Thu, 08 Mar 2018 13:28:34 GMT

I mean still not sure what the problem is?

The image matches the yellow column you have for Flag? Is that not right? What does it needs to look like?

Re: Flagging by ID and Date field

karan_kn — Thu, 08 Mar 2018 13:38:13 GMT

If the Primary ID is same, the whichever secondary ID is lower to be flag as New

Re: Flagging by ID and Date field

sunny_talwar — Thu, 08 Mar 2018 14:01:21 GMT

Try this

Table:

LOAD RowNo() as RowNum,

LOAD * INLINE [

Primary_ID, Secondary_ID, Product Name, Date

1, A101, AeehjgrAA, 2/8/2018

1, A102, BBffggdfgB, 2/7/2018

2, A105, AAfgjhdfA, 2/21/2018

3, A104, BBfd6ghgB, 2/14/2018

3, A107, AfgdjjhfAA, 2/7/2018

3, A109, BBdfhgdfB, 2/9/2018

4, A111, AAdfgfgA, 2/10/2018

4, A114, BBdffhkgB, 2/10/2018

5, A116, AAdfdfgA, 2/15/2018

6, A120, BBdkfgB, 2/18/2018

6, A118, AAdjhkfgA, 2/18/2018

6, A122, BBdrefgB, 2/18/2018

7, A127, AAdjmfA, 2/23/2018

7, A132, BBfytudgB, 2/3/2018

8, A147, HHdbcfgH, 2/13/2018

8, A155, KKdfujgK, 2/17/2018

8, A169, LLdhhhfgL, 2/3/2018

9, A175, LLdfjkgL, 2/9/2018

9, A170, LLdffgL, 2/9/2018

];

FinalTable:

LOAD *,

If(Primary_ID = Previous(Primary_ID), 'Repeat', 'New') as Flag

Resident Table

Order By Primary_ID, Date, Secondary_ID;

DROP Table Table;

Re: Flagging by ID and Date field

karan_kn — Thu, 08 Mar 2018 14:07:38 GMT

Thanks for your kind help working well.

Re: Flagging by ID and Date field

sunny_talwar — Thu, 08 Mar 2018 14:08:13 GMT

No problem at all buddy