Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
zach_paz
Contributor III
Contributor III

JOINING Tables Issue... Please Help!

Hello,


I am trying to combine to tables as follows:


Table1 Contains:

geoid     Distance     Institution

01          1                       a

02          3                       a

03          6                       a

01          4                        b

03          7                        b


Table2 Contains:

Institution     Company     Program     Degree

a                   aa                51               1

a                   aa                47               1

a                   aa                47               2

b                   bb               13               3

b                   bb               13               5


I Need the final table to look like this:

Institution     Company     Program     Degree     Geoid     Distance

a                   aa                51               1               01           1

a                   aa                51               1               02           3

a                   aa                51               1               03           6

a                   aa                47               1               01           1

a                   aa                47               1               02           3

a                   aa                47               1               03           6

a                   aa                47               2               01           1

a                   aa                47               2               02           3

a                   aa                47               2               03           6

b                   bb               13               3               01           4

b                   bb               13               3               03           7

b                   bb               13               5               01           4

b                   bb               13               5               03           7

This is the Load Script I have right now:


Table1:

LOAD geoid,
      Distance,
      Institution
FROM

outer join

Table2:
LOAD Institution,
      Company,
      Program,
      Degree
FROM

Thanks in advance!

1 Solution

Accepted Solutions
swuehl
MVP
MVP

I think you are almost there (if you address the correct source), just use the JOIN as LOAD prefix:

RESULT:

LOAD * INLINE [

geoid,     Distance,     Institution

01,          1,                       a

02,          3,                       a

03,          6,                       a

01,          4,                        b

03,          7,                        b

];

JOIN (RESULT) LOAD * INLINE [

Institution,     Company,     Program ,    Degree

a,                   aa,             51,               1

a,                   aa,             47,               1

a,                   aa,             47,               2

b,                   bb,             13,               3

b,                   bb,             13,               5

];

View solution in original post

13 Replies
Anonymous
Not applicable

Zach

Both your load statements reference the same source xlsx, as in:

     FROM


Is that a typo in your post or the cause of your problem ?

Best Regards,     Bill

zach_paz
Contributor III
Contributor III
Author

that is a typo... they are from two different tabs in an excel document

swuehl
MVP
MVP

I think you are almost there (if you address the correct source), just use the JOIN as LOAD prefix:

RESULT:

LOAD * INLINE [

geoid,     Distance,     Institution

01,          1,                       a

02,          3,                       a

03,          6,                       a

01,          4,                        b

03,          7,                        b

];

JOIN (RESULT) LOAD * INLINE [

Institution,     Company,     Program ,    Degree

a,                   aa,             51,               1

a,                   aa,             47,               1

a,                   aa,             47,               2

b,                   bb,             13,               3

b,                   bb,             13,               5

];

zach_paz
Contributor III
Contributor III
Author

I tried this:

Result:
LOAD geoid,
      Distance,
      Institution
FROM

(ooxml, embedded labels, table is geoid);

JOIN (Result)

LOAD Institution,

      Company,
      Degree,
      Program
FROM

(ooxml, embedded labels, table is program);

Still doesn't work. Does it matter that I am not using an "INLINE" load?

swuehl
MVP
MVP

What exactely do you mean with 'doesn't work'?

Error during execution, no Result table, too few lines, too many lines?

To make above work, the values in Institution in both tables need to match.

Anonymous
Not applicable

Zach

What kind of 'doesn't work' ?

Do you get an error?  If so what ?

Do you get the wrong result data?  If so what ?

Best Regards,     Bill

albertovarela
Partner - Specialist
Partner - Specialist

Hello

Try this:

T1:

LOAD geoid,

     Distance,

     Institution

FROM

C:\yourdoc.xlsx

(ooxml, embedded labels, table is Sheet2);

T2:

left join (T1)

LOAD

     Institution,

     Company,

     Program,

     Degree

FROM

C:\yourdoc.xlsx

(ooxml, embedded labels, table is Sheet1);

zach_paz
Contributor III
Contributor III
Author

Bill, it did not produce the result table that I am looking for.

zach_paz
Contributor III
Contributor III
Author

Alberto, it resulted in no data for Company, Program and Degree.