Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
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;
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;
@Vegar @BrunPierre thanks. it works.