tmp: LOAD Year, No, Name, ID, Client, Category, BU, Segment, Type, Role, [New Target 1], [New Target 2], [New Target 3], [New Target 4], [Old Target 1], [Old Target 2], [Old Target 3], [Old Target 4], //[Change Date and Time], TimeStamp(TimeStamp#([Change Date and Time],'DD-MM-YYYY hh:mm:ss')) as [Change Date and Time] FROM [Change file.xlsx] (ooxml, embedded labels, table is Sheet1); filter: inner keep load No, max([Change Date and Time]) as [Change Date and Time] resident tmp group by No; drop table filter;
Check the above code
Thank you so much for your help. I am getting max changed date records. But I need to keep the old records where changes not applied same as it is in the output.
I am adding more detail here.Please find the below screen shot.
The Original Records input file contains the Actual data. The change log file contains the changes on Original records file rows at different time stamp.
In the output I need to keep the max changed date records where changes applied and need to keep original records same as it is Based on Staff no, ID and Account Category fields .
Please help me on this logic.
Thanks in advance.