Qlik Community

Qlik Education Discussions

Discussion Board for collaboration related to Qlik Education.

Not applicable

Concatenate function in Qlikview

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 IDClock Start Date Lot NumberPTReporter Source TypeCase TypeCountry
2013SA10649110-OCT-2013E3015Y03ChillsSPONTANEOUSUnSolicitedCanada
2013SA10649110-OCT-2013E3015Y03PainSPONTANEOUSUnSolicitedCanada
2013SA10649110-OCT-2013E3016Y03ChillsSPONTANEOUSUnSolicitedCanada
2013SA10649110-OCT-2013E3016Y03PainSPONTANEOUSUnSolicitedCanada
2013SA10791122-OCT-2013E3015Y06Infusion site erythemaSPONTANEOUSUnSolicitedArgentina
2013SA10791124-OCT-2013E3015Y06Infusion site erythemaSPONTANEOUSUnSolicitedArgentina
2013SA107911 E3015Y06Infusion site erythemaSPONTANEOUSUnSolicitedArgentina

and I want it to look like

Case IDClock Start Date Lot NumberPTReporter Source TypeCase TypeCountry
2013SA10649110-OCT-2013E3015Y03;
E3016Y03
Chills;
Pain
SPONTANEOUSUnSolicitedCanada
2013SA10791210-Oct- 2013;
24-OCT-2013
E3015Y06Infusion site erythemaSPONTANEOUSUnSolicitedArgentina

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

1 Solution

Accepted Solutions
alk
Not applicable

Re: Concatenate function in Qlikview

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

8 Replies
Gysbert_Wassenaar
Not applicable

Re: Concatenate function in Qlikview

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


talk is cheap, supply exceeds demand
Not applicable

Re: Concatenate function in Qlikview

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.

alk
Not applicable

Re: Concatenate function in Qlikview

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

Not applicable

Re: Concatenate function in Qlikview

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 IDCase typeClock Start DateCountryLot NumberPTReporter Source type
2013SA106491UnSolicited;UnSolicited;UnSolicited;UnSolicited10/10/2013;10/10/2013;10/10/2013;10/10/2013Canada;Canada;Canada;CanadaE3015Y03;E3015Y03;E3016Y03;E3016Y03Chills;Chills;Pain;PainSPONTANEOUS;SPONTANEOUS;SPONTANEOUS;SPONTANEOUS
2013SA107911UnSolicited;UnSolicited;UnSolicited10/22/2013;10/24/2013Argentina;Argentina;ArgentinaE3015Y06;E3015Y06;E3015Y06Infusion site erythema;Infusion site erythema;Infusion site erythemaSPONTANEOUS;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 IDCase typeClock Start DateCountryLot NumberPTReporter Source type
2013SA106491UnSolicited10/10/2013;10/10/2013;10/10/2013;10/10/2013CanadaE3015Y03;E3015Y03Chills; PainSPONTANEOUS
alk
Not applicable

Re: Concatenate function in Qlikview

Use Concat (DISTINCT ... ) instead

Not applicable

Re: Re: Concatenate function in Qlikview

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.

 

alk
Not applicable

Re: Concatenate function in Qlikview

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

Not applicable

Re: Concatenate function in Qlikview


Thanks  a lot Agis, It worked.

Community Browser