Logic Request

Hi Team,

I need your support on below query.

Please find attached input and required output tab for your reference.

I am tried with cross tab and summarize tools but i am unable to finished this with proper outcome - could you please help me out on this, thanks.

Logic request.xlsx

Common Use Cases

Accepted answers

All comments

Jaganmohan

Input:

Identifier	Source	group1	group2
AED	amer	0.273	78.78
AED	euro	0.275	79.23
RED	amer	89.3	89.2
RED	euro	88.2	89.8
BMW	amer	98.5	97.5
BMW	euro	98.7	97.388
GHE	amer	63	89

Required Output given below

Identifier	type	amer	euro	type	amer	euro
AED	Group1	0.273	0.275	Group2	78.78	79.23
RED	Group1	89.3	88.2	Group2	89.2	89.8
BMW	Group1	98.5	98.7	Group2	97.5	97.388
GHE	Group1	63		Group2	89

BS_THE_ANALYST

@Jaganmohan one way, but certainly not dynamic:

Manipulation_.yxzp

binu_acs

@Jaganmohan One way of doing this

Transpose_020223.yxmd

Jaganmohan

Thanks for the logic - what happens in case if i have more groups.

example : group1,group2,group3,group4,Group5

Jaganmohan

Hi - Thanks for your support.

May i know the further logic please- what if more then 2 groups ?
like apart from only group1 and group2 ...we have group3, group4 ,group5.

BS_THE_ANALYST

@Jaganmohan just extend on from @binu_acs previous answer like so:

You can repeat the process

Transpose_020223_binuacs_solution_extended.yxmd

BS_THE_ANALYST

@Jaganmohan i.e. 4 groups:

Transpose_020223_binuacs_solution_extended.yxmd

Jaganmohan

thanks for logic - but for me it showing as below error.

Christina_H

How about this? It's not exactly your required layout, but it is dynamic.

Crosstab.yxmd

BS_THE_ANALYST

@Jaganmohan oh! I forgot to mention, you can't actually have multiple fields with the same names. When I use the Join tool, I renamed the new Type column to Type<space space> etc. Everytime I created another join, I renamed it to Type<space space space>. Literally adding another space(s) to the end of type. So to the human eye, it looks like Type, but in reality its like Type _ _. Then the next Type _ _ _, Type _ _ _ _ and so on. Just a way to make field names actually look identical.

Jaganmohan

Thanks - Added Multiple join tool functionality.
and finally in the select tool i am using your space idea - Thanks for that.

Christina_H

Improved my previous solution to give you a fully dynamic option, incorporating the spaces in the names to give you the correct headers.

Crosstab logic.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703