Sorting by date and transpose

[Amelia_96]

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.

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

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;