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.
