topic Re: joining 2 tables having different column name loaded from different source in QlikView

joining 2 tables having different column name loaded from different source

sayadutt — Mon, 25 Aug 2014 20:13:13 GMT

Hi All,

I am loading from 2 different sharepoint locations and the column names are different. However i need to join these 2 tables based on 1 column name.

Note, I can't change the source column names .

for eg:

LOAD [field 1], [Associate]

from <sharepoint>;

LOAD [field 2], [Associate Name]

from <sharepoint>;

Now i need to join these 2 tables.

[field1] [field2] [Associate Name]

Please help.

Thanks

Re: joining 2 tables having different column name loaded from different source

Clever_Anjos — Mon, 25 Aug 2014 20:14:50 GMT

Rename your fields, so they have the same name

LOAD [field 2], [Associate Name] as [Associate] from;

Re: joining 2 tables having different column name loaded from different source

JonnyPoole — Mon, 25 Aug 2014 20:16:21 GMT

Use aliases to name them the same in the script.This will join on Associate. you can left join as well as join.

Table:

LOAD

[field 1],

[Associate]

from <sharepoint>;

join (Table)

LOAD [field 2],

[Associate Name] as [Associate]

from <sharepoint>;

Re: joining 2 tables having different column name loaded from different source

Anonymous — Mon, 25 Aug 2014 20:23:10 GMT

Hello,

Try using the following scheme:

Table1:

LOAD * INLINE [

Key, A, VL1

1, A1, 1000

2, A2, 2000

3, A3, 3000

1, A1, 6000

];

Table2:

LOAD * INLINE [

Key, C, Desc

1, A1, Money

2, A2, Cheque

3, A3, Boleto

];

LEFT JOIN (Table1)

LOAD

Key,

Desc

Resident Table2;

DROP Table Table2;

Re: joining 2 tables having different column name loaded from different source

denwo2010 — Mon, 25 Aug 2014 20:23:57 GMT

Hi, Then at the end of the script add the following syntax Drop Field [Associate] Thanks

Re: joining 2 tables having different column name loaded from different source

sayadutt — Mon, 25 Aug 2014 21:31:02 GMT

Hi Jonathan,

When I followed your above approach, Field2 values are coming as blank.

Actual Code:

table1:

LOAD

[Practice Area ],

[Associate Working On ]

FROM

Tab23:

NOCONCATENATE

LOAD Distinct SubField([Associate Working On ],'; ') as [Single Associate Working On],

[Practice Area ],

Resident table1 ;

join(Tab23)

LOAD

[Resource Status ],

[ Associate Name ] as [Single Associate Working On]

FROM

in above code, [Resource Status ] is coming as - (hyphen), although it has values.

Can you please suggest, what I did wrong.

Re: joining 2 tables having different column name loaded from different source

JonnyPoole — Mon, 25 Aug 2014 21:44:34 GMT

Probably it found no [Associate Name ] values that matched up with the results of the Subfield() field ( [Single Associate Working On].

as a test comment out and add the drop table below. Then reload and see what you are getting for [Associate Name ] . Do the values look like [Single Associate Working On] values ?

//join(Tab23)

LOAD

[Resource Status ] ,

[ Associate Name ]

FROM

drop table table1:

Re: joining 2 tables having different column name loaded from different source

sayadutt — Mon, 25 Aug 2014 22:10:26 GMT

Yes, they are exactly same. Both these fields contain names of associates, which I am trying to join.

Re: joining 2 tables having different column name loaded from different source

JonnyPoole — Tue, 26 Aug 2014 01:48:16 GMT

Ok. Would you be able to attach the sample ?