topic Re: Merging rows into one column in load script in QlikView

Merging rows into one column in load script

Anonymous — Tue, 30 Apr 2013 14:18:30 GMT

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

Re: Merging rows into one column in load script

Gysbert_Wassenaar — Tue, 30 Apr 2013 14:28:03 GMT

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;

Re: Merging rows into one column in load script

nagaiank — Tue, 30 Apr 2013 14:28:23 GMT

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

;

Re: Merging rows into one column in load script

jagan — Tue, 30 Apr 2013 14:35:18 GMT

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.

Re: Merging rows into one column in load script

Anonymous — Tue, 30 Apr 2013 14:39:47 GMT

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>

Re: Merging rows into one column in load script

Gysbert_Wassenaar — Tue, 30 Apr 2013 14:47:27 GMT

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

Re: Merging rows into one column in load script

Anonymous — Tue, 30 Apr 2013 15:05:48 GMT

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.