Data Transformation

[Abiswal86]

Hi There,

Please refer the image, as shown in rows, i have files coming annually for each month with updates in data(e.g. Quantity column value changed for April 2022 to April 2023). In these cases i need to work out a logic to just pick data from latest files.

Please advice.

Thanks in Advance!. 

[JamesCharnley]

Hi @Abiswal86 

I think it's a little bit difficult to know how to solve this exactly based on just the png provided, for example not knowing how it's being updated. If it's as simple as the final instance of the date is always the correct quantity, you could just use a Sample Tool Grouped by FileName taking the last 1 row. If that order isn't consistent but everything is stored in the same directory, then maybe we can use the directory tool, sorting by creation time descending and sampling the first one. Just two options that could work depending on the exact circumstances but can't guarantee either in this instance.

[Abiswal86]

Hi @JamesCharnley 

I have a folder which contains file from each month startting 2022 and i am consolidating their data into a single file which have column "file name" and going forward i want to put a check here to take the latest file from the consolidated file.

Please refer the image.

Thanksfor your immediate response.

[NMangera]

@Abiswal86 ,

agree with James. With just the .png for context rather challenging to understand the constraints. In addition to the above, if you want/need the remaining data in the workflow an alternative is to convert filename column to a date field and then use the summarise tool to isolate the most recent date.

[Abiswal86]

Thanks for your input @JamesCharnley , @NMangera , out of all the other fields only these were the fields which keeps changing the rest all remain same.