topic Re: Help with concatenate in QlikView

Help with concatenate

Tue, 08 Sep 2015 08:05:54 GMT

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 — Tue, 08 Sep 2015 08:09:11 GMT

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

sasiparupudi1 — Tue, 08 Sep 2015 08:10:02 GMT

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

sunny_talwar — Tue, 08 Sep 2015 08:10:17 GMT

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

HTH

Best,

Sunny

Re: Help with concatenate

Colin-Albert — Tue, 08 Sep 2015 08:17:47 GMT

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 — Tue, 08 Sep 2015 09:48:58 GMT

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

Wed, 09 Sep 2015 01:36:28 GMT

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.