Segregate data my month

[kauser]

Hello, 

i have a input file that will update couple of times in every week/month and based on that i have to create pivot table. for example- end of each week input file will update with new data that means this file will have four table of data and will have four pivot table. challenge is  how can i make a workflow that will create separate new pivot table every time with new data from same input file. 

Input file - (2 weeks of data)

January 7th
ITEM1	ITEM2
A	45345
B	68017.5
C	102026.3
D	153039.4
A	789879
B	1184819
C	1777228
D	2665842
January 15th
ITEM1	ITEM3
A	768
B	1536
C	3072
D	6144
A	789879
B	1184819
C	1777228
D	2665842

expected output- (2 pivot table. first from Jan7th and second from Jan15th) 

Row Labels	Sum of ITEM2
A	835224
B	1252836
C	1879254
D	2818881
Grand Total	6786195
Row Labels	Sum of ITEM3
A	768
B	1536
C	3072
D	6144
Grand Total	11520

[binuacs]

@kauser question the Jan 7th and 15th data from one file or separate files?

[kauser]

Same input file 

[binuacs]

Question : Are the January 7th and 15th headers also in the input file? I am looking for some identifier to segregate the data

[kauser]

yes header will be there too for both Jan7th and Jan 15th 

[Yoshiro_Fujimori]

Hi @kauser 

From the viewpoint of data normalization, it might be easier to re-format the data like below.

I know in the real world it is sometime difficult to change the input data, but if you can change the input data to follow the first normal form,

Alteryx Tools (say Summarize, Cross Tab etc.) can deal with the data much easier than the original data format.

So I would recommend looking into this. Good luck.

[Qiu]

@kauser 
You are really asking Alteryx to do that it is not good at. 😁

If we have to, may we can try with a batch macro as below.

[lwolfie]

Did these solutions work?  An additional step to get it closer to your output would be to use the reporting tools after the solutions above.