8 Replies Latest reply: Sep 24, 2014 6:20 PM by Kapil Bhutada RSS

    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.

        • Re: Concatenate function 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];

            • 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.

                • Re: Concatenate function in Qlikview
                  Agis Kalogiannis

                  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
                  [C:\Users\Bhuka01\Desktop\sampling 2.xlsx]
                  (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

                      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