Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Amelia_96
Contributor III
Contributor III

Sorting by date and transpose

Hi, I am trying to transpose my data to column 1, 2 & 3 but following the oldest date to newest accordingly. Can anyone help how do i write my code to get the output.

This is sample data: So after transpose, column 1 should be AN14, column2=AN08 and column3=NN69, following the order of the dates.

Amelia_96_0-1626335980408.png

But what i get in my qlikview is like this: Not following the right order.

Amelia_96_1-1626336092947.png

My current code:

AQ8:
LOAD distinct NBR1, CD1, DATE1, STRDOCCD2
Resident AQ8
order by NBR1, CD1, DATE1;

AQ9:
LOAD distinct CD1, SubField(STRDOCCD1, '/', 1) as C1,
SubField(STRDOCCD1, '/', 2) as C2, SubField(STRDOCCD1, '/', 3) as C3;
LOAD CD1, Concat(STRDOCCD2, '/', STRDOCCD2) as STRDOCCD1
Resident AQ8
group by CD1
order by CD1, DATE1;

 

 

Labels (1)
0 Replies