Concatenate function in Qlikview

[#1]

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.

[Gysbert_Wassenaar]

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];

[#3]

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.

[Agis-Kalogiannis · Accepted solution]

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

[#5]

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

[Agis-Kalogiannis]

Use Concat (DISTINCT ... ) instead

[#7]

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.

[Agis-Kalogiannis]

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

[#9]

Thanks  a lot Agis, It worked.