Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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
that is a typo... they are from two different tabs in an excel document
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
];
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?
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.
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
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);
Bill, it did not produce the result table that I am looking for.
Alberto, it resulted in no data for Company, Program and Degree.