Help with concatenate

[#1]

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

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

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 · Accepted solution]

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]

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

[#7]

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.