Related to finding summary and then writing back that summary

[nazuk]

Hello All,

 I am new to alteryx, i have a business case, where i have a table in the following format.

State	Zip Code	tax collected(lakhs)
Rajasthan	302019	67
Rajasthan	302019	78
Rajasthan	302029	98
Rajasthan	302029	56
Rajasthan	302019	76
Maharashtra	411027	7
Maharashtra	411098	85
Maharashtra	411027	55
Maharashtra	411678	7

The user want the summary of tax just below the category . for e.g.

State	Zip Code	tax collected(lakhs)
Rajasthan	302019	67
Rajasthan	302019	78
Rajasthan	302029	98
Rajasthan	302029	56
Rajasthan	302019	76
Total	302029	154
	302019	221
Maharashtra	411027	7
Maharashtra	411098	85
Maharashtra	411027	55
Maharashtra	411678	7
Total	411098	85
	411027	62
	411678	7

can we do this . Please suggest

[markcurry · Accepted solution]

I think this workflow should do what you are looking for...

[nazuk]

Thanks Mark. Can you also suggest me one thing where i am stuck. So basically i have a input file which is a excel file with a dynamic name as TSE082QT_DD_MM_YYYY_HHMM . This excel file has only one sheet and that also have dynamic name as sheet name. So i tried when only the file name is dynamic, we can use wildcards. But in my case the only solution we could get is through "dynamic input tool" . Since i am a beginner i am unable to understand how we have to create a formula. Can you help me on this as well?

[markcurry]

Unfortunately reading it *.xlsx won't work if you have a dynamic sheet name, and I don't think the Dynamic Input tool will work either.  I'd use created a batch macro, see here for details :

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-To-Import-Multiple-Excel-Sheets...

Batch macros can be difficult for beginners, so another option might be the 'Wildcard XLSX Input' tool within the Crew macros, available here: http://www.chaosreignswithin.com/

[nazuk]

Hello @markcurry ,

I am still stuck at this level, as user added few more formulas for creating the output:

So now if input is 

State	Zip Code	tax collected(lakhs)	ID
Rajasthan	302019	67	P1
Rajasthan	302019	78	P1
Rajasthan	302029	98	P2
Rajasthan	302029	56	P1
Rajasthan	302019	76	P2
Maharashtra	411027	7	P1
Maharashtra	411098	85	P2
Maharashtra	411027	55	P1
Maharashtra	411678	7	P1

The output should  be:

Date	State	Zip Code	tax collected(lakhs)	Population(crores)	ID
1-Feb-20	Rajasthan	302019	67	14	P1
1-Feb-20	Rajasthan	302019	78	23	P1
1-Feb-20	Rajasthan	302029	98	21	P2
1-Feb-20	Rajasthan	302029	56	23	P1
1-Feb-20	Rajasthan	302019	76	12	P2
Total			375	93
P1			201	60
P2			174	33
1-Feb-20	Maharashtra	411027	7	78	P1
1-Feb-20	Maharashtra	411098	85	98	P2
1-Feb-20	Maharashtra	411027	55	56	P1
1-Feb-20	Maharashtra	411678	7	76	P1
Total			154	308

Below are the rules:

Group by State:

1. if group name start with R, then :

1.Just after all records  write sum for population and tax.

2. leave a blank row after this

3. Group by id P1 and P2 and write sum for pulation and tax seprately for both the id.

4. Leave a blank row after this.

2. if group name start with R, then :

1.Just after all records  write sum for population and tax.

2. leave a blank row after this

Can you please help me on this?

[markcurry]

I think the attached workflow will do it for you.  So each section is given a sort number, so the normal detail lines are 0, Total Line = 1, Blank Line = 2, and the ID subtotals are 3,4....