Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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];
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.
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
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 |
Use Concat (DISTINCT ... ) instead
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.
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
Thanks a lot Agis, It worked.