Creating complex Pivot Tables

[Abhii2658]

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 Location	Group by Field A	Group by Field B	Group by Field C	Count of  Field D
Location 1	AB10	123	ABC	10	This should should show the entire record that consists of 50 fields
	AB10	123	ABC	10
	AB11	123	ABC	10
	AB12	123	ABC	10
	AB13	123	ABC	10
	AB14	123	ABC	10
	AB14	123	ABC	10
Location 2	CD10	123	ABC	10
	CD11	123	ABC	10
	CD12	123	ABC	10
	CD13	123	ABC	10
	CD14	123	ABC	10
	CD15	123	ABC	10
	CD16	123	ABC	10
	CD17	123	ABC	10
Location 3	GH11	123	ABC	10
	GH12	123	ABC	10
	GH13	123	ABC	10
	GH14	123	ABC	10
	GH15	123	ABC	10
			Total	200

[danilang]

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]

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]

Field A

Field B

Field C

Field D

Field E

Field G

Locatrion

Section

Class

Field K

Field L

Field M

Field N

Field O

Field P

Field Q

Field R

Field S

Field T

Field U

Field V

Field W

Field X

Field Y

Field Z

Field AA

Field AB

Field AC

Field AD

Field AE

Field AF

Field AG

Field AH

X

1

0

Target - Yes

Yes

Data

900

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

XX

2

0

Target - NO

NO

Data

900

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

X

3

0

Target - NO

NO

Data

900

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

XX

4

0

Target - NO

NO

Data

900

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

10

0

Target - NO

NO

Data

900

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

A

10

0

Target - NO

NO

Data

900

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

A

10

1

Target - Yes

NO

Data

900

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

A

10

1

Target - Yes

NO

Data

900

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

A

10

5

1

Target - Yes

NO

Data

900

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

B

10

4

1

Target - Yes

NO

Data

900

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

B

10

43

1

Target - Yes

NO

Data

900

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

B

10

1

1

Target - NO

NO

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

1

Target - NO

NO

Location 1

100

A

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

1

Target - NO

Yes

Location 1

900

B

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

0

Target - Yes

Yes

Location 1

900

B

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

0

Target - Yes

Yes

Location 2

800

B

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

0

Target - Yes

Yes

Location 2

800

B

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

0

Target - Yes

Yes

Location 2

800

B

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Filed Name

Filer

Filed A

Show 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 G

Locatrion

Section

Field J

Field K

Field L

Field M

Field N

Field O

Field P

Field Q

Field R

Field S

Field T

Field U

Field V

Field W

Field X

Field Y

Field Z

Field AA

Field AB

Field AC

Field AD

Field AE

Field AF

Field AG

Field AH

0

Target - Yes

Yes

Location 1

900

A

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

0

Target - Yes

Yes

Location 2

800

B

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

0

Target - Yes

Yes

Location 2

800

B

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

0

Target - Yes

Yes

Location 2

800

B

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Pivot Result

Location

Class

Section

Count

Location 1

A

100

1

B

900

2

[Abhii2658]

Filed Name

Filer

Filed A

Show 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 G

Location

Section

Class

Field K

Field L

Field M

Field N

Field O

Field P

Field Q

Field R

Field S

Field T

Field U

Field V

Field W

Field X

Field Y

Field Z

Field AA

Field AB

Field AC

Field AD

Field AE

Field AF

Field AG

Field AH

0

Target - Yes

Yes

Location 1

900

A

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

0

Target - Yes

Yes

Location 2

800

A

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

0

Target - Yes

Yes

Location 2

800

B

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

0

Target - Yes

Yes

Location 2

800

B

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Data

Pivot Result

Location

Class

Section

Count

Location 1

A

900

1

Location 2

A

800

1

B

800

2

[Elias_Nordlinder]

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.

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

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

3. Group by Location, Class and Section

and count the total.

Input:

Output:

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

[Abhii2658]

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 · Accepted solution]

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.

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

Regards

Elias