Hi all,
I know there are some answers to this predicament on here but want some more clarity.
I have a file that is 1 GB full of sales transactions, however I just need to sum up all the repeating UPCs into one sum total. Sample size of an example of how the data is displayed.
UPC | DESCRIPTION | FAC CODE | REGION | ENTITY | QTY SHIPPED |
UPC1 | UPCDEC1 | ATL | SO | ENT1 | 1 |
UPC1 | UPCDEC1 | ATL | SO | ENT1 | 2 |
UPC2 | UPCDEC2 | SAR | FL | ENT2 | 3 |
UPC3 | UPCDEC3 | ATL | SO | ENT2 | 4 |
UPC2 | UPCDEC2 | AUR | RM | ENT1 | 5 |
I need in theory to output, UPC1>UPCDEC1>ATL>SO>ENT1>1+2=3
So on and so forth. But I also need to output the same thing but the total of ENT2 separately so I DONT want that in the total for ENT1. Preferably that to output in another column on the same total row.
I'd like it look like the below:
UPC | DESCRIPTION | FAC CODE | REGION | ENTITY1 | QTY SHIPPED SUM TOTAL 1 | ENTITY2 | QTY SHIPPED SUM TOTAL 2 |
UPC1 | UPCDEC1 | ATL | SO | ENT1 | 3 | ENT2 | 0 |
UPC2 | UPCDEC2 | SAR | FL | ENT1 | 0 | ENT2 | 3 |
UPC3 | UPCDEC3 | ATL | SO | ENT1 | 0 | ENT2 | 4 |
UPC2 | UPCDEC2 | AUR | RM | ENT1 | 5 | ENT2 | 0 |
Any help would be appreciated thanks!
Solved! Go to Solution.
Hi @mrich93
I've Attached a workflow that gets the job done.
Let me know if it works for you.
Regards!
Would I need a dynamic input after the text input before select tool to input my data in?
That depends.
If you transactional data is located in just one file (i.e. .xlsx; .csv, other), you will just need an input tool. In my example, I've used a text input since I don't have an external file to input, but you do.
If otherwise, you may need an dynamic input tool in order to input all your data.
Got it! It's actually YXDB file. So I'm trying to mimic your workflow. Getting stumped here on cross-tab and mutli-field formula. If I can't separate the separate entities if I use an input tool. Where do I specify the two distinct Entities to cross-tab to Multi-field formula? Does that make sense?
Thanks!
Hi @mrich93
I am not sure I follow. In order to input your data (.yxdb) you should use an input data tool.
Also, the first cross-tab tool is use to separate each entity into new columns. It will have the following configuration:
Right after that, the mutli-field formula is used to add 0 to all the field that are 0 in the columns you select (in this case, you will select the Entity columns).
Then, each individual cross tab tool is used to get the look that you want as your output data. In this workflow, you will deal with one entity at a time.