topic Re: Join 3 tables in QlikView

Join 3 tables

Mon, 11 Feb 2013 21:34:50 GMT

Hi,

I have three tables each with a key field (CustomerID) but within those tables there are 2 other fields (Title, Status) that are the same but have different values. How does one combine them so that I do not get the $Syn 1. I need to combine them for reporting purposes but when i do that i have 3 key fields. If I run a left join, then I am unable to view the distinctions between the fields.

I believe there is a simple solution.

Any help is greatly appreciated.

Thank you,

Nicholas

Re: Join 3 tables

Tue, 12 Feb 2013 08:23:06 GMT

there are many other fields in Your tables right?

or there are just CustomerID, Title and Status?

if not You can prepare new key CustomerID_Title_Status

Re: Join 3 tables

er_mohit — Tue, 12 Feb 2013 09:23:12 GMT

hiiii

see the attached file

Re: Join 3 tables

Tue, 12 Feb 2013 09:50:16 GMT

LOAD CustomerID,

title,

status

FROM

C:\Users\vishwaranjan\Desktop\tablejoin.xlsx

(ooxml, embedded labels, table is Sheet1);

noConcatenate

LOAD CustomerID,

title,

status

FROM

C:\Users\vishwaranjan\Desktop\tablejoin.xlsx

(ooxml, embedded labels, table is Sheet2);

noConcatenate

LOAD CustomerID,

title,

status

FROM

C:\Users\vishwaranjan\Desktop\tablejoin.xlsx

(ooxml, embedded labels, table is Sheet3);

Re: Join 3 tables

Tue, 12 Feb 2013 09:55:58 GMT

why don't You just create simple loop:

for each vSheet in 'Sheet1', 'Sheet2', 'Sheet3'

LOAD CustomerID,

title,

status

FROM

(ooxml, embedded labels, table is $(vSheet));

next vSheet;

Re: Join 3 tables

Tue, 12 Feb 2013 10:02:08 GMT

vishwaranjan

by the way...

concatenate work with resident table?

i've got an issue...

robo:

LOAD * INLINE [

Dealer, KEY, Date, WholeSale, Retail

D1, D101-01-2013, 01-01-2013, 50, 2

D1, D103-01-2013, 03-01-2013, 10, 5

];

final:

NoConcatenate LOAD Dealer,

KEY,

Date,

WholeSale,

Retail

resident robo;

all the fields are still keys and those two tables are linked together:(

could You explain that?

Re: Join 3 tables

er_mohit — Tue, 12 Feb 2013 10:17:33 GMT

hiii

there an issue of concatenate.

in this there is all fields are keys using no concatenation thet will be treated as differe..

but for either writing concatenate or not (automatic) it will concat data due to the same field name..and remove keys .....

Re: Join 3 tables

Tue, 12 Feb 2013 10:28:19 GMT

i see...

anybody else got complex explanation?

Re: Join 3 tables

manideep78 — Tue, 12 Feb 2013 13:49:29 GMT

Hi,

Concatenation doesnot bother whether it is a resident load or data loaded from any other source. If there are matching columns in both tables then automatically both tables will concatenate. If we use noconcatenate then synthetic keys occur and thier number is based on the common fields in both tables.