Onboard files dynamically.

Question

Hi Team,

I need to onboard csv files and apply few logic ( lets take the eg of sales). Logic is same for all the 3 months of the quarter. This is to be performed at the end of every month till the end of a quarter ..

eg: for Q123 :

End of Jan ME i will hav one file.

End of Feb ME i will have 2 files ( Jan -1; Feb -1)

End of Mar ME i wil have 3 files (Jan -1, Feb-1, Mar 1).

By end of Jan ME sales is to be consumed for Jan ME file for every business date of Jan alone.

With the same code, once Feb DAta is available i should be able to generate the sales for every business date of Jan and Feb ..

similarly same code should generate the output for every business date of  Jan Feb and Mar by the end of Mar.

Input :

File S.NoItemDateSales   File11Grocery12/01/202320   File12Medicine24/01/202345   File23Grocery16/02/202333   File24Medicine11/02/202367   File35Grocery09/03/202322   File36Medicine17/03/202348   File37Beverage15/03/202388           output       Item12/01/202324/01/202316/02/202311/02/202309/03/202317/03/202315/03/2023Grocery20 33 22  Medicine 45 67 48 Beverage      88

Appreciate your help. thanks in advance

SrinivasanSugumaran · Answer

Thanks Prometheus for the revert.

But the data  are in 3 separate files and not in single file having a col which tells which input it is sourced from. File 1 , file 2, and file 3.

and while running for Jan i wil have only one input file. and while running for feb i wil hav only 2 inputs and only in mar i wil hav 3 input files.

and the code should run even irrespective of the inputs (for first month it should run with one input, same code shold run successfully when we have 3 input files for last month without any code tweak).

Prometheus · Answer

@SrinivasanSugumaran The first thing I did was convert the date to an actual date using the DateTime parse tool, then deselect the original field. After that, I used a Transpose tool to pivot the data with key columns S.No, Item, and Date. Then I pivoted the data back using a Cross Tab tool while grouping by Item, using the Date field as column headers, and the sum of Value for the data.

Onboard Files.yxmd