concatenate and then join

[clovati]

t:

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';

j:

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

[sunny_talwar]

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

[sunny_talwar]

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

[sunny_talwar · Accepted solution]

Try this:

t:

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';

j:

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;

[ramoncova06]

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

t:

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;

j:

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;