I am new to Alteryx, so struggling to find a solution elsewhere on the message board. I have two columns "Category" and "Ledger Name" with anywhere from 5 - 15 distinct values in them.
Example below:
Category Ledger Name
PPE US Ledger
PPE US Ledger
PPE Canada Ledger
Prepaid China Ledger
Prepaid Germany Ledger
Goodwill US Ledger
I want an output that finds all the unique values in both columns and then creates two columns in the following format below:
Category Ledger Name Closing Balance
PPE US Ledger A sumifs formula based on both Category / Ledger name on another tab output
PPE Canada Ledger A sumifs formula based on both Category / Ledger name on another tab output
PPE China Ledger A sumifs formula based on both Category / Ledger name on another tab output
PPE Germany Ledger A sumifs formula based on both Category / Ledger name on another tab output
Prepaid US Ledger A sumifs formula based on both Category / Ledger name on another tab output
Prepaid Canada Ledger A sumifs formula based on both Category / Ledger name on another tab output
Prepaid China Ledger A sumifs formula based on both Category / Ledger name on another tab output
Prepaid Germany Ledger A sumifs formula based on both Category / Ledger name on another tab output
I have tried cross-tab and the unique tools, but was not able to determine which was correct or what combination will get me here.
Solved! Go to Solution.
Hi @taxguy33
Do you have a sample of your data from your input and desired output just to make it more clear?
Cheers,
Hi @taxguy33
Here's a possible solution
It starts by finding the unique categories and ledgers and performing a cross join using the Append Fields tool. This is joined back to the original data on Category and ledger name. The joined records are summarized and the unjoined records gets a 0 Closing Balance before being unioned and sorted
This input data
gives this output
Dan