topic Re: MID / Textbetween Issue in QlikView

MID / Textbetween Issue

neha_potdar — Sat, 01 Nov 2014 05:15:44 GMT

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

Re: MID / Textbetween Issue

PrashantSangle — Sat, 01 Nov 2014 05:30:47 GMT

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

Re: MID / Textbetween Issue

MarcoWedel — Sat, 01 Nov 2014 10:02:03 GMT

Instead of

MID (col1,1,4)

you could use

Left(col1,4)

Re: MID / Textbetween Issue

maxgro — Sat, 01 Nov 2014 12:04:18 GMT

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,

Resident Tmp1;

Table2:

load

col2 as ID,

Resident Tmp2;

DROP Table Tmp1, Tmp2;

Re: MID / Textbetween Issue

neha_potdar — Sat, 01 Nov 2014 23:08:05 GMT

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.

Re: MID / Textbetween Issue

swuehl — Sat, 01 Nov 2014 23:55:35 GMT

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.

Re: MID / Textbetween Issue

neha_potdar — Sun, 02 Nov 2014 00:05:07 GMT

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.

Re: MID / Textbetween Issue

anbu1984 — Sun, 02 Nov 2014 03:48:49 GMT

Can you provide sample where join is not working?

Re: MID / Textbetween Issue

neha_potdar — Mon, 03 Nov 2014 15:30:22 GMT

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.