Load Script

[Sharbel]

Hello,

I have the following transactions table (Source table):

I am trying to write load script that achieves the followings: for each Clinet_no, Client_name and Transaction_date the script loads only the  row with the most recent File_Date.

Given the above source table, the desired load script will generate the following table :

Any ideas?

Best regards,

Sharbel

[maheshkuttappa · Accepted solution]

This code works based on sample data. What is the size of the data??  

T:
LOAD
File_Date,
Serial_no,
Client_no,
Client_name,
Transaction_date
FROM [lib://DataFiles/Sample.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);

T2:
Inner Join (T)
Load
Max(File_Date) as File_Date,
Client_no,
Transaction_date
Resident T Group By Client_no, Client_name,Transaction_date;

[sidhiq91 · Accepted solution]

@Sharbel  Please use the below code to get the desired output.

NoConcatenate
Temp:
Load Date(File_Date,'DD/MM/YYYY') as File_Date,
Serial_no,
Client_no,
Client_name,
Date(Transaction_Date,'DD/MM/YYYY') as Transaction_Date

Inline [
File_Date,Serial_no,Client_no,Client_name,Transaction_Date
18/11/2023,23111600,93600011,Benjamin Carter,18/11/2023
15/11/2023,23111511,93600011,Benjamin Carter,12/11/2023
13/11/2023,23111309,93600011,Benjamin Carter,12/11/2023
12/11/2023,23111208,93600011,Benjamin Carter,12/11/2023
13/11/2023,23111309,39500001,Emma Johnson,08/11/2023
12/11/2023,23111208,39500001,Emma Johnson,08/11/2023
08/11/2023,23111806,39500001,Emma Johnson,08/11/2023
];

NoConcatenate
Temp1:
Load Date(Max(File_Date),'DD/MM/YYYY') as Max_File_Date,
Date(Max(File_Date),'DD/MM/YYYY') & Client_no as Key,
Client_no,
Transaction_Date
Resident Temp
group by Client_no,Transaction_Date;

Inner join (Temp1)
Load Date(File_Date,'DD/MM/YYYY') & Client_no as Key,
Serial_no,
Client_name
Resident Temp;

Drop field Key from Temp1;

Drop table Temp;

Exit Script;

If this resolves your issue, please like and accept it as a solution.