6 Replies Latest reply: Sep 8, 2015 9:36 PM by May Javier RSS

    Help with concatenate

    May Javier

      I tried to apply concatenate function in my Qlik Sense script. It loaded successfully but when I tried to check the result data it's giving duplicate results. One line item showing all the values from the original table but with null values contained in the added column. The another line item was just showing the value of the added column with nothing against the original table.

       

      btw, here is the sample code:

       

      Jobs:

      SQL

        Select

              j.createddate AS JobCreatedDate,

              j.id AS JobID.............

       

      Closures:..........

      SQL

        Select

          outcome AS JobOutcome,

          job_id AS JobIDClosures......................

      .........

      Concatenate (Jobs)

      Load

      JobIDClosures AS JobID,

      JobOutcome AS Outcome

      RESIDENT Closures;

       

      Here is the sample outcome(attached):

        • Re: Help with concatenate
          Sunny Talwar

          Try using Join instead of Concatenate

           

          Jobs:

          SQL

            Select

                  j.createddate AS JobCreatedDate,

                  j.id AS JobID.............

           

          Closures:..........

          SQL

            Select

              outcome AS JobOutcome,

              job_id AS JobIDClosures......................

          .........

          Join(Jobs)

          Load

          JobIDClosures AS JobID,

          JobOutcome AS Outcome

          RESIDENT Closures;

          • Re: Help with concatenate
            Sasidhar Parupudi

            please share a sanple qvw

             

            may be try

             

            Jobs:

            SQL

              Select

                    j.createddate AS JobCreatedDate,

                    j.id AS JobID.............

             

            noconcatenate

             

            Closures:..........

            SQL

              Select

                outcome AS JobOutcome,

                job_id AS JobIDClosures......................

            .........

            Concatenate (Jobs)

            Load

            JobIDClosures AS JobID,

            JobOutcome AS Outcome

            RESIDENT Closures;

            • Re: Help with concatenate
              Colin Albert

              Use a mapping table and applymap to bring the Outcome into your data.

              Don't join - use Applymap instead

               

               

              Closures:

              SQL

                Select

                  outcome AS JobOutcome,

                  job_id AS JobIDClosures......................

              .........

               

              Outcome_Map:

              Mapping Load

                   JobIDClosures ,

                   JobOutcome

              RESIDENT Closures;

               

              Jobs:

              load

                    createddate AS JobCreatedDate,

                   id AS JobID   ,

                   ...

                   applymap('Outcome_map', id) as Outcome;

              SQL

                Select

                      j.createddate ,

                      j.id.............

              • Re: Help with concatenate
                Gabriel Oluwaseye

                Hi,

                What I would check in this instance is to ensure that JobID is unique and distinct.

                Also, load the table again in QlikView.

                 

                 

                Jobs:

                LOAD DISTINCT

                     *

                ;

                 

                SQL

                  Select

                        j.createddate AS JobCreatedDate,

                        j.id AS JobID.............

                 

                Concatenate (Jobs)

                LOAD DISTINCT

                     *

                ;

                SQL

                  Select

                    outcome AS JobOutcome,

                    job_id AS JobIDClosures......................

                • Re: Help with concatenate
                  May Javier

                  Thanks to all who looked into the post and especially to those responded! All your answers were helpful!

                  In my scenario, I find that using LEFT JOIN settles my duplicates issue.