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: 
Anonymous
Not applicable

How to Separate fields with same ID into two columns

I have the following data in my QV table

  

IDSubject
1234English
1234Maths
2345French
2334English

I would my table to show distinct ID and associated subjects - so this is want I'd like to output

  

IDSubject1subject 2
1234EnglishMaths
2345French
2334English

Any suggestions please

Thanks

JazzyK

1 Solution

Accepted Solutions
andydietler
Partner - Creator
Partner - Creator

Are there only a maximum of 2? You could create a more dynamic loop if there are more, but this should work for two:

NewTable:

LOAD

     ID,

     Subject AS Subject1

Resident CurrentTable

WHERE Not ID = Previous(ID)

ORDER BY ID, Subject;

Left Join (NewTable)

LOAD

     ID,

     Subject AS Subject2

Resident CurrentTable

WHERE ID = Previous(ID)

ORDER BY ID, Subject;

View solution in original post

3 Replies
andydietler
Partner - Creator
Partner - Creator

Are there only a maximum of 2? You could create a more dynamic loop if there are more, but this should work for two:

NewTable:

LOAD

     ID,

     Subject AS Subject1

Resident CurrentTable

WHERE Not ID = Previous(ID)

ORDER BY ID, Subject;

Left Join (NewTable)

LOAD

     ID,

     Subject AS Subject2

Resident CurrentTable

WHERE ID = Previous(ID)

ORDER BY ID, Subject;

Anonymous
Not applicable
Author

No I have 4870 IDs and 4999 subjects. I just provided a sample. I really wanted do this as an expression in a table.

Thanks

Jasmit

Anonymous
Not applicable
Author

Thank you. That worked perfectly. I managed to put it into the script.