Sort the column by datewise

rupaliqlik — Wed, 25 Nov 2020 16:16:04 GMT

Dear Experts,

I have some issue.

ROW_ID         NEW_VALUE               REC_LAST_UPD         ROW_NUMBER
1-8FAKUNG Unqualified               01-04-2017 06:40:58 1
1-8FAKUNG Verification Pending       01-04-2017 06:47:20 2
1-8FAKUNG Follow-Up               01-04-2017 07:08:48 3
1-8FAKUNG Customer Concern       03-04-2017 07:07:03 4
1-8FAKUNG Negative Closure       06-04-2017 06:22:59 5

I have data against ROW_ID wise but my user want data in horizontal format but in datewise ascending order

ROW_ID STATUS1 STATUS2 STATUS3 STATUS4 STATUS5 REC_LAST_UPD1 REC_LAST_UPD2 REC_LAST_UPD3 REC_LAST_UPD4 REC_LAST_UPD5
1-8FAKUNG Unqualified Verification Pending Follow-Up Customer Concern Negative Closure 01-04-2017 06:40:58 01-04-2017 06:47:20 01-04-2017 07:08:48 03-04-2017 07:07:03 06-04-2017 06:22:59 \

here is below my code:
NoConcatenate
SIEBEL_CX_AUDIT_TRAIL:
LOAD * Inline
[
ROW_ID
1-8FAKUNG
];
LEFT JOIN(SIEBEL_CX_AUDIT_TRAIL)

LOAD DISTINCT
      REC_ROW_ID AS ROW_ID,
      NEW_VALUE,
      AUDIT_ROW_ID,
      REC_LAST_UPD
FROM

(QVD) WHERE Exists(ROW_ID,REC_ROW_ID);

NoConcatenate
SIEBEL_CX_AUDIT_TRAIL1:
LOAD ROW_ID,
      NEW_VALUE,
      AUDIT_ROW_ID,
      REC_LAST_UPD
      Resident SIEBEL_CX_AUDIT_TRAIL WHERE NOT MATCH(NEW_VALUE,'New') AND LEN(TRIM(ROW_ID))>=1 ;
      DROP Table SIEBEL_CX_AUDIT_TRAIL;

NoConcatenate
SIEBEL_CX_AUDIT_TRAIL2:
LOAD DISTINCT
      NEW_VALUE,
      ROW_ID,
      REC_LAST_UPD,
      ROWNO()AS ROW_NUMBER
Resident SIEBEL_CX_AUDIT_TRAIL1 ORDER BY REC_LAST_UPD ASC ;
drop Table SIEBEL_CX_AUDIT_TRAIL1;

NoConcatenate
SIEBEL_CX_AUDIT_TRAIL3:
LOAD
ROW_ID,
Concat(DISTINCT DATE(REC_LAST_UPD,'YYYY-MM-DD hh:mm:ss'),',') AS REC_LAST_UPD
Resident SIEBEL_CX_AUDIT_TRAIL2 group by ROW_ID ORDER BY ROW_NUMBER ASC;

left join(SIEBEL_CX_AUDIT_TRAIL3)
LOAD
ROW_ID,
Concat(DISTINCT NEW_VALUE,',') AS STATUS
Resident SIEBEL_CX_AUDIT_TRAIL2 group by ROW_ID ORDER BY ROW_NUMBER asc ;
drop Table SIEBEL_CX_AUDIT_TRAIL2;

exit SCRIPT;

and i found output like this
ROW_ID REC_LAST_UPD
1-8FAKUNG 2017-04-01 06:40:58,2017-04-01 06:47:20,2017-04-01 07:08:48,2017-04-03 07:07:03,2017-04-06 06:22:59

ROW_ID STATUS
1-8FAKUNG Customer Concern,Follow-Up,Negative Closure,Unqualified,Verification Pending

my status are not in correct order,thet are sort by default in alphabetical order.Please give me some suggestion in thuis scenario.

Re: Sort the column by datewise

sunny_talwar — Mon, 19 Jun 2017 10:16:00 GMT

Have you tried to create a pivot table?

Pivot Table ‒ QlikView

topic Sort the column by datewise in QlikView

Sort the column by datewise

Re: Sort the column by datewise