topic Re: Concatenate function in Qlikview in Qlik Learning Discussions

Concatenate function in Qlikview

Tue, 23 Sep 2014 22:55:21 GMT

Hi everyone,

I am beginner in Qlikview and just exploring it. I am not sure if we can use concatenate function in Qlikview. I have the below data

Case ID	Clock Start Date	Lot Number	PT	Reporter Source Type	Case Type	Country
2013SA106491	10-OCT-2013	E3015Y03	Chills	SPONTANEOUS	UnSolicited	Canada
2013SA106491	10-OCT-2013	E3015Y03	Pain	SPONTANEOUS	UnSolicited	Canada
2013SA106491	10-OCT-2013	E3016Y03	Chills	SPONTANEOUS	UnSolicited	Canada
2013SA106491	10-OCT-2013	E3016Y03	Pain	SPONTANEOUS	UnSolicited	Canada
2013SA107911	22-OCT-2013	E3015Y06	Infusion site erythema	SPONTANEOUS	UnSolicited	Argentina
2013SA107911	24-OCT-2013	E3015Y06	Infusion site erythema	SPONTANEOUS	UnSolicited	Argentina
2013SA107911		E3015Y06	Infusion site erythema	SPONTANEOUS	UnSolicited	Argentina

and I want it to look like

Case ID	Clock Start Date	Lot Number	PT	Reporter Source Type	Case Type	Country
2013SA106491	10-OCT-2013	E3015Y03; E3016Y03	Chills; Pain	SPONTANEOUS	UnSolicited	Canada
2013SA107912	10-Oct- 2013; 24-OCT-2013	E3015Y06	Infusion site erythema	SPONTANEOUS	UnSolicited	Argentina

Can anyone guide me how this can be done in qlikview.

Re: Concatenate function in Qlikview

Gysbert_Wassenaar — Wed, 24 Sep 2014 13:01:55 GMT

LOAD

[Case ID],

concat([Clock Start Date],';') as [Clock Start Date],

concat([Lot Number],';') as [Lot Number],

....etc for the other fields...

FROM MySource

GROUP BY [Case ID];

Re: Concatenate function in Qlikview

Wed, 24 Sep 2014 16:24:16 GMT

Thanks a lot for the response. I am uploading the excel file but the code doesnt work, I am getting syntax error, I have attached the screenshot of the same. I really appreciate your help.

Re: Concatenate function in Qlikview

Agis-Kalogiannis — Wed, 24 Sep 2014 18:06:15 GMT

LOAD
[Case ID],
concat ([Clock Start Date],';') as [Clock Start Date],
concat ([Lot Number], ';') as [Lot Number],
concat ([PT], ';') as [PT],
concat ([Reporter Source Type],';') as [Reporter Source type],
concat ([Case Type], ';') as [Case type],
concat ([Country], ';') as [Country]
FROM

(ooxml, embedded labels)

GROUP BY [Case ID];

Hi Kapil

Comments:

1. Remove the , before the FROM statement

2. Remove the ; after the (ooxml, embeeded labels)

3. Add a ; after the GROUP BY statement.

I hope this helps

Agis

Re: Concatenate function in Qlikview

Wed, 24 Sep 2014 18:18:01 GMT

Great Agis that worked. I am feeling good now. Thank you again for your corrections. Do you know how I can just have unique entries for Eg due to concatenation, the same value is separeted and appears like this

Case ID	Case type	Clock Start Date	Country	Lot Number	PT	Reporter Source type
2013SA106491	UnSolicited;UnSolicited;UnSolicited;UnSolicited	10/10/2013;10/10/2013;10/10/2013;10/10/2013	Canada;Canada;Canada;Canada	E3015Y03;E3015Y03;E3016Y03;E3016Y03	Chills;Chills;Pain;Pain	SPONTANEOUS;SPONTANEOUS;SPONTANEOUS;SPONTANEOUS
2013SA107911	UnSolicited;UnSolicited;UnSolicited	10/22/2013;10/24/2013	Argentina;Argentina;Argentina	E3015Y06;E3015Y06;E3015Y06	Infusion site erythema;Infusion site erythema;Infusion site erythema	SPONTANEOUS;SPONTANEOUS;SPONTANEOUS

How we can ensure that only one unique value is captured if its the same entry is repeated in multiple rows like this:

Case ID	Case type	Clock Start Date	Country	Lot Number	PT	Reporter Source type
2013SA106491	UnSolicited	10/10/2013;10/10/2013;10/10/2013;10/10/2013	Canada	E3015Y03;E3015Y03	Chills; Pain	SPONTANEOUS

Re: Concatenate function in Qlikview

Agis-Kalogiannis — Wed, 24 Sep 2014 18:48:14 GMT

Use Concat (DISTINCT ... ) instead

Re: Re: Concatenate function in Qlikview

Wed, 24 Sep 2014 20:05:55 GMT

Thank you Agis once again, There is some mistake when I use the Distinct syntax I cant figure it so I have attached the script.

Re: Concatenate function in Qlikview

Agis-Kalogiannis — Wed, 24 Sep 2014 20:52:15 GMT

It seems to work fine Kapil

Could you try this script please?

LOAD

[Case ID],

concat (Distinct [Clock Start Date],';') as [Clock Start Date],

concat (Distinct [Lot Number], ';') as [Lot Number],

concat (Distinct [PT], ';') as [PT],

concat (Distinct [Reporter Source Type],';') as [Reporter Source type],

concat (Distinct [Case Type], ';') as [Case type],

concat (Distinct [Country], ';') as [Country]

FROM

[C:\Users\Bhuka01\Desktop\sampling 2.xlsx]

(ooxml, embedded labels)

GROUP BY [Case ID];

Thanks

Re: Concatenate function in Qlikview

Wed, 24 Sep 2014 22:20:58 GMT

Thanks a lot Agis, It worked.