Creating a Multi-Level pivot based on conditions

[Pre2479]

Hi Alteryx team, I am really looking forward your help with creating a multi-level pivot table based on locations for the below data (data manipulated). The pivot table needs to produce the sum of accounts based on the following conditions combinations of Locations A and Locations B. LOCATION - A (Location = A and Balance=>0 (Debit)) = total No. of accounts (under Debit Balance and Location A) (Location = A and Balance=<0 (Credit)) = total No. of accounts (under Credit Balance and Location A) (Location = A and Balance=>0 (Debit) and Status=NEW Account) = total No. of New accounts (under Debit Balance and Location A) (Location = A and Balance=<0 (Credit) and Status=NEW Account) = total No. of New accounts (under Credit Balance and Location A) (Location = A and Balance=>0 (Debit)) and Status code=Resolved) = total No. of Resolved status (under Debit Balance and Location A) (Location = A and Balance=<0 (Credit) and Status code=Resolved) = total No. of Resolved status (under Credit Balance and Location A) (Location = A and Balance=>0 (Debit) and Status code<>MISSING ENTRY (does not equal)) = total No. of Updated accounts (under Debit Balance and Location A) (Location = A and Balance=<0 (Credit) and Status code<> MISSING ENTRY (does not equal)) = total No. of Updated accounts (under Credit Balance and Location A) (Location = A and Balance=>0 (Debit)) = sum of USD balance (under Debit Balance and Location A) (Location = A and Balance=<0 (Credit)) = sum of USD balance (under Credit Balance and Location A) (Location = A and Balance=>0 (Debit)) = Highest USD balance (under Debit Balance and Location A) (Location = A and Balance=<0 (Credit)) = Highest USD balance (under Credit Balance and Location A) LOCATION - B (Location = B and Balance=>0 (Debit)) = total No. of accounts (under Debit Balance and Location B) (Location =B and Balance=<0 (Credit)) = total No. of accounts (under Credit Balance and Location B) (Location = B and Balance=>0 (Debit)) and Status=NEW Account) = total No. of New accounts (under Debit Balance and Location B) (Location = B and Balance=<0 (Credit) and Status=NEW Account) = total No. of New accounts (under Credit Balance and Location B) (Location = B and Balance=>0 (Debit) and Status code=Resolved) = total No. of Resolved status (under Debit Balance and Location B) (Location = B and Balance=<0 (Credit) and Status code=Resolved) = total No. of Resolved status (under Credit Balance and Location B) (Location = B and Balance=>0 (Debit) and Status code<>MISSING ENTRY (does not equal))= total No. of Updated accounts (under Debit Balance and Location B) (Location = B and Balance=<0 (Credit) and Status code <>MISSING ENTRY (does not equal))= total No. of Updated accounts (under Credit Balance and Location B) (Location = B and Balance=>0 (Debit)) = sum of USD balance (under Debit Balance and Location B) (Location = B and Balance=<0 (Credit)) = sum of USD balance (under Credit Balance and Location B) (Location = B and Balance=>0 (Debit)) = Highest USD balance (under Debit Balance and Location B) (Location = B and Balance=<0 (Credit)) = Highest USD balance (under Credit Balance and Location B)

[Yoshiro_Fujimori · Accepted solution]

Hi @Pre2479 ,

Though your text is not clear enough to understand the requirement,

I made a draft workflow guessing your intention from the input data and the output image.

Input

Workflow

Usually I use Transpose and Cross Tab tools, if the same grouping is applied.

But in this case, as the pivot is "multi-level", I could not use this method.

Output

"Updated Accounts" is not included as I cannot guess what you expect.

Most of the case, you should be able to get the number with Summary tool, and then join the result in the same manner as above.

Good luck.

[Pre2479 · Accepted solution]

Many many thanks Yoshiro, your solution worked like a charm. thankyou so much.