topic Re: concatenate and then join in QlikView

concatenate and then join

clovati — Fri, 17 Apr 2015 19:43:46 GMT

LOAD PDV,

Conto,

CNS,

BDG

FROM

TESTJoin.xlsx

(ooxml, embedded labels, table is Foglio1)

where Conto='MC';

Concatenate (t)

LOAD PDV,

Conto,

CNS,

BDG

FROM

TESTJoin1.xlsx

(ooxml, embedded labels, table is Foglio1)

where Conto='MC';

LOAD PDV,

//Conto,

CNS as CNSV,

BDG as BDGV

FROM

TESTJoin.xlsx

(ooxml, embedded labels, table is Foglio1)

where Conto='VN';

Concatenate (j)

LOAD PDV,

//Conto,

CNS as CNSV,

BDG as BDGV

FROM

TESTJoin1.xlsx

(ooxml, embedded labels, table is Foglio1)

where Conto='VN';

inner join (t)

load * resident j;

drop table j;

why my values are double?

THANKS!!!

Re: concatenate and then join

sunny_talwar — Fri, 17 Apr 2015 19:52:48 GMT

Because the join is taking place for just one field PDV which is going to come twice because of the concatenation. and when 2 A's in i with 2 A's in j, you end up with 4 A's in your new table.

I hope what I just said make sense.

Best,

Sunny

Re: concatenate and then join

sunny_talwar — Fri, 17 Apr 2015 19:54:18 GMT

You will need to do this inner join on two fields the combination of which is unique for the table so that the number of observations doesn't multiply.

Best,

Sunny

Re: concatenate and then join

sunny_talwar — Fri, 17 Apr 2015 19:56:12 GMT

Try this:

LOAD PDV,

'Join' as Source,

Conto,

CNS,

BDG

FROM

TESTJoin.xlsx

(ooxml, embedded labels, table is Foglio1)

where Conto='MC';

Concatenate (t)

LOAD PDV,

'Join1' as Source,

Conto,

CNS,

BDG

FROM

TESTJoin1.xlsx

(ooxml, embedded labels, table is Foglio1)

where Conto='MC';

LOAD PDV,

'Join' as Source,

//Conto,

CNS as CNSV,

BDG as BDGV

FROM

TESTJoin.xlsx

(ooxml, embedded labels, table is Foglio1)

where Conto='VN';

Concatenate (j)

LOAD PDV,

'Join1' as Source,

//Conto,

CNS as CNSV,

BDG as BDGV

FROM

TESTJoin1.xlsx

(ooxml, embedded labels, table is Foglio1)

where Conto='VN';

inner join (t)

load * resident j;

drop table j;

Re: concatenate and then join

ramoncova06 — Fri, 17 Apr 2015 20:03:40 GMT

Sunindia is right, is because your concatenation is creating multiple rows, so whenever you do the join it bring the rows duplicated, you could try with mapping or by grouping the data before you join them

LOAD PDV,

Conto,

CNS,

BDG

FROM

TESTJoin.xlsx

(ooxml, embedded labels, table is Foglio1)

where Conto='MC';

Concatenate (t)

LOAD PDV,

Conto,

CNS,

BDG

FROM

TESTJoin1.xlsx

(ooxml, embedded labels, table is Foglio1)

where Conto='MC';

tempt:

load

Conto

PDV,

sum(CNS) as CNS,

sum(BDGV as BDG

resident t

group by Conto, PDV;

LOAD PDV,

//Conto,

CNS as CNSV,

BDG as BDGV

FROM

TESTJoin.xlsx

(ooxml, embedded labels, table is Foglio1)

where Conto='VN';

Concatenate (j)

LOAD PDV,

//Conto,

CNS as CNSV,

BDG as BDGV

FROM

TESTJoin1.xlsx

(ooxml, embedded labels, table is Foglio1)

where Conto='VN';

tempj:

load

PDV,

sum(CNSV) as CNSV,

sum(BDGV) as BDGV

resident j

group by PDV;

inner join (t)

load * resident tempj;

drop tables j, tempj;