topic Re: Combine two records into one in QlikView

Combine two records into one

regowins — Wed, 27 Feb 2013 17:37:42 GMT

Hi,

I have two records coming from the source system because one text field column is very large…see attached. The user only wants to see one record instead of two. The data from the source system cannot be changed. Is there a way I can do this in the data model or UI using a concatenate function or similar?

Thanks for the help!

Re: Combine two records into one

Gysbert_Wassenaar — Wed, 27 Feb 2013 17:51:03 GMT

You can do it both in the UI and in the script using the concat function. In the script you need to include the other fields in a group by clause:

Data:

First 1

LOAD No.,

[Date First Received],

[Address 1],

[Address 2],

[Address 3],

CITY,

State,

Zip,

Country,

concat([Company Narrative],' ') as [Company Narrative]

FROM

TB22_20130227_103839.xls

(biff, embedded labels, header is 1 lines, table is [Sheet1$])

group by No., [Date First Received], [Address 1], [Address 2], [Address 3],

CITY, State, Zip, Country ;

In the UI you can use concat in an expression: concat([Company Narrative], ' ')

Re: Combine two records into one

Anonymous — Wed, 27 Feb 2013 17:55:08 GMT

Ashwin,

You can concatenate two or more records into one on load from the first table:

Load

No,

"Date First Recieived",

...

concat("Company Narrative", '') as "Company Narrative"

From ...

GROUP BY

No,

"Date First Recieived",

...

Regards,

Michael

Re: Combine two records into one

regowins — Wed, 27 Feb 2013 18:11:16 GMT

Thanks, both were helpful!