Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
vikasshana
Creator II
Creator II

Transpose or Cross Table in app

HI,

I've the below sample data from excel file.

      India US UK
Test Name Name1 123 456 789
All Sample Sample1 IN245 US089 UK395
Yes Sample2 Sample3 IN507 US812 UK270
Yes Sample4 Sample5   US913 UK294

 

I'm looking for cross table or transpose in my app Load Script and would like to see the output like below.

Test Name Name1 New Column1 New Column2 New Column3
All Sample Sample1 India 123 IN245
All Sample Sample1 US 456 US089
All Sample Sample1 UK 789 UK395
Yes Sample2 Sample3 India 123 IN507
Yes Sample2 Sample3 US 456 US812
Yes Sample2 Sample3 UK 789 UK270
Yes Sample4 Sample5 India 123  
Yes Sample4 Sample5 US 456 US913
Yes Sample4 Sample5 UK 789 UK294

 

@PrashantSangle @tresesco @sunny_talwar 

2 Replies
marcus_sommer

vidyutverma
Partner - Contributor III
Partner - Contributor III

Ah, CrossTable won`t work due to the first row and the way it is transposed. First Row values [123,456,789] look directly mapped to [India,US,UK]. So You have to do it in two steps:

1. Ignore the first row and do the cross Table as suggested by @marcus_sommer 

2. Do a simple Left join with a table having Two columns, Country and corresponding value in Row Test!