Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Re: Help with concatenate

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

HTH

Best,

Sunny

6 Replies

Re: Help with concatenate

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
Honored Contributor III

Re: Help with concatenate

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

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

HTH

Best,

Sunny

Re: Help with concatenate

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
Valued Contributor II

Re: Help with concatenate

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

Re: Help with concatenate

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.

Community Browser