Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
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.