Merging rows into one column in load script

[Anonymous]

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

[Gysbert_Wassenaar]

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;

[nagaiank]

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

;

[jagan]

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.

[Anonymous]

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>

[Gysbert_Wassenaar · Accepted solution]

Try: concat( '<' & Duration & ';' & Category & '>', '$') as "Duration Category"

[Anonymous]

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.