Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
Can any one please help me on below requirement. I have two source files named Original Records input file and Change Records input file
The Original Records input file contains the Actual data. The change Record file contains the changes on Original records file rows at different time stamps.
Daily some of the old records will be updated with new records on different time slots. From those need to pick only Max changed date time records and need to keep the not changed records same as it is based on Staff no, ID and Account Category fields .
I am adding more detail in image.Please find the below screen shot.
Please help me on this logic.
Hi bhavani,
Try the script below.
// Just date transformation from Excel file
tmp:
LOAD [Staff No],
ID,
[Account Category],
date#([Changed Date and Time],'DD-MM-YYYY hh:mm:ss') as [Changed Date and Time]
FROM
(ooxml, embedded labels, table is sheet1);
// New records from Excel file
dataNew:
LOAD [Staff No],
FY,
name,
ID,
[Group Name],
[Account Category],
[Client Coverage],
[Role Type],
[Old Revenue Target],
[Old Sales Target],
[Old Pen Target],
[Old Margin Target],
[Revenue Target],
[Sales Target],
[Margin Target],
[Pen Target],
date#([Changed Date and Time],'DD-MM-YYYY hh:mm:ss') as [Changed Date and Time]
FROM
(ooxml, embedded labels, table is sheet1);
// Gets max date/time from records based on Staff No, ID and Account Category
NoConcatenate
maxChangeDateTime:
Load
[Staff No],
ID,
[Account Category],
max([Changed Date and Time]) as [Changed Date and Time]
Resident tmp
group by [Staff No], ID, [Account Category] ;
drop table tmp;
// Join to see which records must be put in the original file
left join(dataNew)
Load
[Staff No],
ID,
[Account Category],
[Changed Date and Time],
1 as [Flag New Records]
Resident maxChangeDateTime;
// Data from Original records
data:
LOAD [Staff No],
FY,
name,
ID,
[Group Name],
[Account Category],
[Client Coverage],
[Role Type],
[Old Revenue Target],
[Old Sales Target],
[Old Pen Target],
[Old Margin Target]
FROM
(ooxml, embedded labels, table is Sheet1);
// Checks which records must be replaced
left join (data)
Load
[Staff No],
ID,
[Account Category],
[Changed Date and Time]
Resident maxChangeDateTime;
NoConcatenate
finalTable:
Load
*
Resident data
where IsNull([Changed Date and Time]);
Concatenate(finalTable)
Load
*
Resident dataNew
where [Flag New Records]=1;
drop tables maxChangeDateTime,data,dataNew;
Should give you the following image:
