Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have 2 tables with the join Table1.Substring (col1,1,4) = Table2.ID.
I am writing MID in the load statement for table 1 as: Load MID (col1,1,4) AS ID. Would this join work? Because I cannot see both table's data together. Either one of the tables data is displayed. One of the table shows blank data when other one displays data.
Thanks,
Neha
Hi,
If you are joining with comman field name then this outer join should work.
If not can you post your script.
So that it wiil become more clear.
Regards
Instead of
MID (col1,1,4)
you could use
Left(col1,4)
a small example to associate 2 table by ID (with left) , no join
Tmp1:
load * inline [
col1,b
00000000,z
11111111,a
22222222,d
33333333,t
];
Tmp2:
load * inline [
col2,c
1111,a
2222,d
3333,t
4444,m
5555,n
];
Table1:
load
left(col1,4) as ID,
b
Resident Tmp1;
Table2:
load
col2 as ID,
c
Resident Tmp2;
DROP Table Tmp1, Tmp2;
I tried using Inline. It still does not join the 2 tables. The error is I can see only one table's data at a time. Below is the code used. Also, tried using left instead of MID
----------------------------------------
Tmp1: load * inline [ id1, Col2 ];
MainTable1: Load left(id1,4) as ID, Col2 Resident Tmp1;
SQL SELECT id1, Col2 FROM Table1;
DROP Table Tmp1;
--------------------------------------
MainTable2: Load id, Col22;
SQL SELECT id, Col22 FROM Table2;
Thanks for your earlier answers.
So, when looking at the table view (CTRL-T), are the two tables linked?
From your code snippet, I don't see a common key field.
I changed the query little bit. I am now loading data from the qvd file instead if using select in the same tab. I can see the physical join in table viewer but The tables are not joined. The QVD file just has select statement and qvw file has query as below.
Table1:
load LEFT(id1,4) AS ID,
[Col1],
[Col2]
FROM
(txt);
Table 2 already has ID field.
Can you provide sample where join is not working?
This got solved. I was making a wrong join. LEFT(id,4) takes 5 characters from left and 4. When I made this to 3, it extracted 4 characters from left.
Thank you all for your answers.