Join the Alteryx Community’s Maveryx Summer Cup event! Compete, network with others, and earn your gold through a series of challenges from July 24th to August 11th. Learn more about the event here.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Creating complex Pivot Tables

Abhii2658
8 - Asteroid

I have a rather unique requirement here. My data  has 70,000 records and based on the below filters i need to populate the count of the final field and display the entire row for the result. The table looks like this 

 

The first 6 lines are the filters need to get the subset of the 70,000 records and on the basis of that, 4 fields are grouped resulting in the final count field which then displays the entire row. of the total number of fields 

 

 

Field A - Filter Blank          
Field B - Filter Blank          
Field C - Filter 0           
Field D - Filter yes           
Field E- Filter All          
Field F- Filter Yes           
           
Group by LocationGroup by Field A Group by Field BGroup by Field CCount of  Field D      
Location 1AB10123ABC10This should should show the entire record that consists of 50 fields      
 AB10123ABC10      
 AB11123ABC10      
 AB12123ABC10      
 AB13123ABC10      
 AB14123ABC10      
 AB14123ABC10      
Location 2 CD10123ABC10      
 CD11123ABC10      
 CD12123ABC10      
 CD13123ABC10      
 CD14123ABC10      
 CD15123ABC10      
 CD16123ABC10      
 CD17123ABC10      
Location 3GH11123ABC10      
 GH12123ABC10      
 GH13123ABC10      
 GH14123ABC10      
 GH15123ABC10      
   Total 200      
7 REPLIES 7
danilang
19 - Altair
19 - Altair

Hi @Abhii2658 

 

What are the possible values for the filter options in the 1st six rows?  For example: Your first row has "Field A - Filter Blank"  Can the field name change?  Can the criteria change to Non-blank, 0, non-zero, etc.

 

Can you also supply some sample input(pre-filter) and the output you're looking for?

 

Dan

Abhii2658
8 - Asteroid

Hello @danilang thanks for the reply. 

 

No the field names and filter values do not change at all. If the Filter selects "0", thats the only result I am interested in. It does not change to 1,2 or anything else. 

Sample in the next comment. 

 

Sample file is below - I want the pivot results which shows all the fields in the output. 

I hope i am making sense here. 

 

Abhii2658
8 - Asteroid
Field A Field B Field C Field D Field E Field GLocatrionSection ClassField KField LField MField NField OField PField QField RField SField TField UField VField WField XField YField ZField AAField ABField ACField ADField AEField AFField AGField AH
 X10Target - Yes YesData900DataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataData
 XX20Target - NONOData900DataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataData
 X30Target - NONOData900DataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataData
 XX40Target - NONOData900DataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataData
 10 0Target - NONOData900DataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataData
A10 0Target - NONOData900DataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataData
A10 1Target - Yes NOData900DataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataData
A10 1Target - Yes NOData900DataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataData
A1051Target - Yes NOData900DataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataData
B1041Target - Yes NOData900DataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataData
B10431Target - Yes NOData900DataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataData
B1011Target - NONODataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataData
   1Target - NONOLocation 1 100ADataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataData
   1Target - NOYes Location 1 900BDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataData
   0Target - Yes Yes Location 1 900BDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataData
   0Target - Yes Yes Location 2800BDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataData
   0Target - Yes Yes Location 2800BDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataData
   0Target - Yes Yes Location 2800BDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataData
                                 
Filed Name Filer                                
Filed AShow only blanks                                
Field B Show only blanks                                
Field C Show only blanks                                
Field D Show only 0                               
Field E Target Yes                                
Field G  No                               
                                 
                                 
Outout                                
                                 
Field A Field B Field C Field D Field E Field GLocatrionSection Field JField KField LField MField NField OField PField QField RField SField TField UField VField WField XField YField ZField AAField ABField ACField ADField AEField AFField AGField AH
   0Target - Yes Yes Location 1 900ADataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataData
   0Target - Yes Yes Location 2800BDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataData
   0Target - Yes Yes Location 2800BDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataData
   0Target - Yes Yes Location 2800BDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataData
                                 
                                 
Pivot Result                                 
                                 
LocationClass SectionCount                              
Location 1A1001                             
 B9002                             
Abhii2658
8 - Asteroid
Filed Name Filer                                
Filed AShow only blanks                                
Field B Show only blanks                                
Field C Show only blanks                                
Field D Show only 0                               
Field E Target Yes                                
Field G  Yes                               
                                 
                                 
Filtered Output                                
                                 
Field A Field B Field C Field D Field E Field GLocationSection ClassField KField LField MField NField OField PField QField RField SField TField UField VField WField XField YField ZField AAField ABField ACField ADField AEField AFField AGField AH
   0Target - Yes Yes Location 1 900ADataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataData
   0Target - Yes Yes Location 2800ADataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataData
   0Target - Yes Yes Location 2800BDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataData
   0Target - Yes Yes Location 2800BDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataDataData
                                 
                                 
Pivot Result                                 
                                 
LocationClass SectionCount                              
Location 1A9001                             
                                 
Location 2 8001                             
 B8002                             
Elias_Nordlinder
11 - Bolide

Hello @Abhii2658 ,

 

I did not find the sample file that produced the required output in your posts.

But I have created workflow that should work dynamically on the input you have.


(You can do this with Pivot (Transpose/Cross Tab) as well, but I did it without.

 

 

Elias_Nordlinder_0-1629620326981.png

 

1. Create a flag column with your requirements:

 

-> If Field A, B or C is NULL, D is 0 and Field E contains Yes

then 1 else NULL().

 

 

IF 
(ISNULL([Field A ])
AND ISNULL([Field B ]) 
AND ISNULL([Field C ]) 
AND [Field D ] = 0
AND CONTAINS([Field E ],"Yes"))
THEN 1
ELSE NULL()
ENDIF

 

Elias_Nordlinder_0-1629628896976.png

 

 

2. Filter on the Flag Column to only show rows where the Flag is 1 
(Where all the filter conditions are satisfied).

 

Elias_Nordlinder_1-1629620833784.png

 

 

3. Group by Location, Class and Section

and count the total.

 

Elias_Nordlinder_3-1629620522230.png

Input:

Elias_Nordlinder_4-1629620628419.png

 

Output:

 

Elias_Nordlinder_1-1629620360442.png

 

//Let me know if it solves your requirements 🙂
Regards
Elias

 

Abhii2658
8 - Asteroid

Thank you @Elias_Nordlinder 

The logic is perfect, works. But in the output i want it to display the entire rows (50 off fields)for the counts. 

Where ever count is 2 

Row1 - All data 

Row 2 - All data 

 

Count 200 

Row 1 - All data 

.

.

.

.

Row 200 - All data 

Elias_Nordlinder
11 - Bolide

Hello again @Abhii2658 ,

 

Thank you for your additional input.

I have created a new workflow with the additional requirements.

 

1. Added a Count separately, joined it back to the original after have created the count

column, to keep all the original rows.

 

2. Kept all the rows after the join instead of removing the additional fields.

 

 

Elias_Nordlinder_0-1629633618176.png

 

Elias_Nordlinder_2-1629633707346.png

 

 

 

//Let me know if this better works for your data 🙂

Regards

Elias

 

Labels