I am trying to to totalize for a key (sales document) the Total Vol and keep ALL others columns. I can do that using PIVOT but the pivot results only have the key and the total by key, I need to keep all the others column (additional to the pivot transform, as the customer name, invoice date, etc.)
I’m not sure what you want the output to look like. Should it have the same number of rows, but an extra column with the sum of TotalVol for each document? Or one row for each unique value of Sales document?
What output do you want for this example input:
| sales document | Vol01 | Vol02 | TotalVol |
|---|---|---|---|
| 123456 | 1 CAS | 1 CAS | |
| 234567 | 2 CAS | 2 CAS | |
| 234567 | 1 CAS | 1 CAS | |
| 345678 | 1 CAS | 1 CAS | |
| 345678 | 3 CAS | 3 CAS |
Extra column for each unique value of Sales Document, but the first option is more general and only need to apply UNIQUE to get the second option
To get an extra column with a total for each document just:
- do a Pivot
- Join the pivot data back to the original data
- tidy up column and column names (not shown)
total-key.transform (3.1 KB)
Is that what you wanted?
As you say, for option 2 you can use the Unique transform.
Perfect! Thanks…Could be possible to “create” meta transformations (this is to save a set of transforms, maybe could be a suggestion to future functionality …
Do you have particular sequences of transforms you use again and again? Can you give me an example?