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: 
neha_potdar
Contributor
Contributor

MID / Textbetween Issue

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

8 Replies
PrashantSangle


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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
MarcoWedel

Instead of

MID (col1,1,4)


you could use


Left(col1,4)

maxgro
MVP
MVP

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;

neha_potdar
Contributor
Contributor
Author

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.

swuehl
MVP
MVP

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.

neha_potdar
Contributor
Contributor
Author

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.

anbu1984
Master III
Master III

Can you provide sample where join is not working?

neha_potdar
Contributor
Contributor
Author

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.