Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help with concatenate

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):

1 Solution

Accepted Solutions
sunny_talwar

Look here for difference between Join and Concatenate: Understanding Join, Keep and Concatenate

HTH

Best,

Sunny

View solution in original post

6 Replies
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;

sasiparupudi1
Master III
Master III

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;

sunny_talwar

Look here for difference between Join and Concatenate: Understanding Join, Keep and Concatenate

HTH

Best,

Sunny

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

Gabriel
Partner - Specialist III
Partner - Specialist III

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

Not applicable
Author

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.