1 Reply Latest reply: Jun 19, 2017 6:16 AM by Sunny Talwar RSS

    Sort the column by datewise

    RUPALI ETHAPE

      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
      [E:\QLIK_VIEW\RAW_QVD\SIEBEL_CX_AUDIT_TRAIL.QVD]
      (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.