Pivot records without totals

Hello,

i am wraping my head trying to find a solution converting a table into a tabular form, which contains totals on records/different columns where we might need/not their data.

In the same table, we have cases where different columns are a sum of the others, and some are single ones.
example, F5 is a sum of F6+F7 and although we do not need F5 total, we need its "header" value as a column, as in result file.

Attached you have the intended result screen, as well as the .yxdb table.

Thank you so much for this,

result.png

table.png

test_table.yxdb

Transformation

Accepted answers

CoG

Here is another version that should fix the issues you're having.

Sample Workflow.yxzp

All comments

caltang

Like so?

For jcardoso.yxzp

jcardoso

Hi,
as in the result.png file attached in the post.
In your solution, we still have the totals. In your result table, for example records 41/42 should be one aggregate record, with 25 units, as:

Italy; Workstations; Desktop Workstations; 25

28=25+3, 57=56+1, 30=30+0, 25=25+0,...Tablet PC=> 316=316....

CoG

Here is another version that should fix the issues you're having.

Sample Workflow.yxzp

caltang

Simple, either add a Select or use a formula expression in Dynamic Select to rid of the "totals" fields.

Like so:

For jcardoso.yxzp

jcardoso

@caltang thank you for this, your solution might work in this case, although, the thing is that you never know which ones do you need to update for the next iteration/file. The transformation of the file will be part of a macro which transforms other files. It's unberable if we need to update in each one of them.

caltang

Well, if you can expand your scope, that would be great. What constitutes total is my question now, because if it's the 1st N of every word that is the same, then are there exception cases?

Maybe lay it out more and I'll add a dynamic select function for you to capture the names dynamically so you don't have to manually update them. It's possible.

What I've shown you above is relevant to your single use case, so if you can give more information, maybe @CoG and I can better help. Otherwise we just keep going back and forth.

caltang

Ah nevermind, I see that you've already accepted @CoG 's solution. Cheers

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703