Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have source data coming like below:
Client ID | Date | Duration | Category |
123 | 4/29/2013 | 10 | SD-MF |
123 | 4/29/2013 | 20 | SD-RET |
123 | 4/29/2013 | 15 | SD-OUTBOUND |
123 | 4/29/2013 | 10 | SD-OUTBOUND |
123 | 4/29/2013 | 5 | SD-OUTBOUND |
We want to to reduce the data into one row by adding these values in to one column as delimeted values and this data is very low level only final screen we want to parse this and show in the UI.. Apart from looping each and every record is there any way we can do this directly in the load script?.
Format we are expecting is :
Client ID | Date | Duration | Category |
123 | 4/29/2013 | 10;20;15;5 | SD-MF;SD-RET;SD-OUTBOUND |
Thanks
Dasu.G
Try: concat( '<' & Duration & ';' & Category & '>', '$') as "Duration Category"
Try
Table1:
Load
[Client ID],
date#(Date,'MM/DD/YYYY') as Date,
concat(Duration, ';') as Duration
concat(Category, ';') as Category
From ...somewhere...
Group By [Client ID], Date;
You can use the following script:
LOAD
[Client ID], Date, Concat(Duration,';') as Durations, Concat(Category,';') as Categories Resident yourtablename Group By [Client ID], Date
;
Hi,
Try like this
Temp:
LOAD
*,
1 AS Temp;
LOAD * INLINE [
ClientID,Date,Duration,Category
123,4/29/2013,10,SD-MF
123,4/29/2013,20,SD-RET
123,4/29/2013,15,SD-OUTBOUND
123,4/29/2013,10,SD-OUTBOUND
123,4/29/2013,5,SD-OUTBOUND ];
Data:
LOAD
ClientID,
Date,
Concat(Duration, ';') AS Duration,
Concat(Category, ';') AS Category
RESIDENT Temp
GROUP BY ClientID, Date;
DROP TABLE Temp;
Hope this helps you.
Regards,
Jagan.
Thanks for the quick response.Is there any way I can maintain relation also in this data structure. Like I have created new column which has duration and category together with row is seperated by $ where as columns are seperated by ";"?.
Client ID | Date | Duration Category |
123 | 4/29/2013 | <10;SD-MF>$<20;SD-RET> |
Try: concat( '<' & Duration & ';' & Category & '>', '$') as "Duration Category"
Too much to ask. Is there any way I can parse this text back to a table structure when some one clicks on client id. no further drill down.