Cross tab values

[rohini]

Hi All,

I have got the below data from transpose tool after that I am using cross tab tool to make the column month the header and values as the numeric values.

but the option that I could give is the sum, but I want the same values.

[grazitti_sapna]

Hi @rohini , the output does not match with the input you provided, anyways you can convert the values from double to v_string then use cross tab tool you will get an option to concatinate the records. In your case the relation is many to one.

Please refer to the screenshot and let me know in case the requirement is different.

Thanks.

[DavidP · Accepted solution]

So, looking at your data, I can see that there are multiple groups of 2 where Year, Month, Line of Business and Name are the same with only value being different, as highlighted below.

The CrossTab tool will look to aggregate these groups, either by sum or average (for numbers) or concatenate (for strings). The way to get around this is to create another differentiation - in this case I use the Tile tool with the option set to Unique Value based on all those fields above. TileSequenceNumber then gives me the unique identifier that I can combine with Month to give me the columns I want.

In the CrossTab tool I can then use this new field for the column names and group by Year, Month, Line of Business and Name, which gives the result shown below.

[kohliangjun]

Hi David, 

Building on your workflow. Since i am experiencing the same issue, your workflow rly helped. However i would like to have 1 column each for each month instead of (mth 1_1, mth 1_2) 

in that case, what changes do i have to make to your workflow to achieve that? Thanks and hope to hear from you soon!

Regards,

Liang Jun