Flagging by ID and Date field

[karan_kn]

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

];

[sunny_talwar · Accepted solution]

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;

[karan_kn]

Thanks It's perfect

[karan_kn]

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;

[sunny_talwar]

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

Not sure what you mean Karan?

[karan_kn]

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'

[karan_kn]

stalwar1‌

[sunny_talwar]

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?

[karan_kn]

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

[sunny_talwar]

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;