Filter Type matching with another Type

[rob_vander]

Hi All,

I have below data. I want to filter data for Type A,B, C, D but only those records where for the same account ID, there is a record for Type 'Z' for the same Date.  e.g. for AccountID=1,  on 15/02/2023 there is record for Type A and it has record for Type Z as well, so pick record of Type A only for Date 15/02. 2023 as it can have multiple records for other days. This logic applies to Type A, B, C & D. As I just need data for Type A,B,C & D ,we can ignore other Types. For one account id any Type can only have single record for any Date

How can I write a script to achieve this?

Load * Inline [
AccountID, Type, Date
1, A, 14/02/2023
1, A, 15/02/2023
1, Z, 15/02/2023
1, B, 20/02/2023
1, Z, 20/02/2023
1, C, 21/02/2023
1, D, 22/02/2023
1, Z, 22/02/2023
1, E, 23/02/2023
1, Z, 23/02/2023

2, A, 20/02/2023
2, C, 21/02/2023
2, Z, 21/02/2023
2, B, 22/02/2023
2, Z, 22/02/2023
2, C, 23/02/2023
2, D, 24/02/2023
2, G, 25/02/2023];

Output:

AccountID	Type	Date
1	A	15/02/2023
1	B	20/02/2023
1	D	22/02/2023
2	C	21/02/2023
2	B	22/02/2023

[Vegar · Accepted solution]

1. You can try this approach.

Source:

Load * Inline [

AccountID, Type, Date

1, A, 14/02/2023

1, A, 15/02/2023

1, Z, 15/02/2023

1, B, 20/02/2023

1, Z, 20/02/2023

1, C, 21/02/2023

1, D, 22/02/2023

1, Z, 22/02/2023

1, E, 23/02/2023

1, Z, 23/02/2023

2, A, 20/02/2023

2, C, 21/02/2023

2, Z, 21/02/2023

2, B, 22/02/2023

2, Z, 22/02/2023

2, C, 23/02/2023

2, D, 24/02/2023

2, G, 25/02/2023];

Table:

NoConcatenate LOAD AccountId,Date RESIDENT Source WHERE Type='Z';

Left join (Table) LOAD * RESIDENTJAG Source WHERE Match(Type, 'A', 'B', 'C','D');

Drop table Source;

[BrunPierre]

Hi, perhaps as below.

NoConcatenate
Final:
LOAD AccountID,
Date

Resident SoureTable
Where Type = 'Z';

Inner Join
LOAD *
Resident SoureTable
Where Match(Type, 'A', 'B', 'C', 'D');

DROP Table SoureTable;

[rob_vander]

@Vegar @BrunPierre  thanks. it works.