Onboard files dynamically.

[SrinivasanSugumaran]

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.No	Item	Date	Sales
File1	1	Grocery	12/01/2023	20
File1	2	Medicine	24/01/2023	45
File2	3	Grocery	16/02/2023	33
File2	4	Medicine	11/02/2023	67
File3	5	Grocery	09/03/2023	22
File3	6	Medicine	17/03/2023	48
File3	7	Beverage	15/03/2023	88
output
Item	12/01/2023	24/01/2023	16/02/2023	11/02/2023	09/03/2023	17/03/2023	15/03/2023
Grocery	20		33		22
Medicine		45		67		48
Beverage							88

Appreciate your help. thanks in advance

[Prometheus]

@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.

 

 

[SrinivasanSugumaran]

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).