topic Re: Inline join syntax - need help in QlikView

Inline join syntax - need help

Anonymous — Fri, 05 Feb 2016 18:59:44 GMT

Hi,

What's the correct syntax for joining two inline tables?

Supposed I have these:

STATE:

LOAD * INLINE

[

STATE

];

CITY:

LOAD * INLINE

[

STATE, CITY

MA, BOSTON

IL, CHICAGO

FL, MIAMI

];

[ CODE HERE ]

How do I write the code to join STATE and CITY and place the code inside the [ CODE HERE ] area? I know you can add

the JOIN clause before the "CITY:" in above like but I want to write the statement AFTER the two tables are created.

LEFT JOIN (STATE)

CITY:

LOAD * INLINE

[

STATE, CITY

MA, BOSTON

IL, CHICAGO

FL, MIAMI

];

Thanks!

Re: Inline join syntax - need help

sunny_talwar — Fri, 05 Feb 2016 19:02:24 GMT

Not sure what you trying to do, but may be this;

STATE:

LOAD * INLINE

[

STATE

];

CITY:

LOAD * INLINE

[

STATE, CITY

MA, BOSTON

IL, CHICAGO

FL, MIAMI

];

JOINED_TABLE:

NOCONCATENATE

LOAD *

RESIDENT STATE;

LEFT JOIN (JOINED_TABLE)

LOAD

RESIDENT CITY;

DROP TABLES, CITY, STATE;

Re: Inline join syntax - need help

Fri, 05 Feb 2016 19:04:01 GMT

You can join in the second the Load statement before.

STATE:

LOAD * INLINE

[

STATE

];

Left Join (STATE)

CITY:

LOAD * INLINE

[

STATE, CITY

MA, BOSTON

IL, CHICAGO

FL, MIAMI

];

Why you need to join after two tables loaded ?

Re: Inline join syntax - need help

PradeepReddy — Fri, 05 Feb 2016 19:07:20 GMT

what you are trying to achieve, by doing so..?

Re: Inline join syntax - need help

Anonymous — Fri, 05 Feb 2016 19:30:22 GMT

Your sample worked. Well two reason I want to do this. First, I want to learn how to write this syntax.

Second, I may have 5 or 10 tables in the app. But, there are instances where I may need to join Table1,

Table7 and Table10 in order to produce Table11 and the join needs to happen after all the tables have been created.

Thanks

Re: Inline join syntax - need help

Anonymous — Fri, 05 Feb 2016 19:36:03 GMT

One question I have is: what does "NOCONCATENATE" do as in ?

JOINED_TABLE:

NOCONCATENATE

LOAD *

RESIDENT STATE;

Re: Inline join syntax - need help

sunny_talwar — Fri, 05 Feb 2016 19:38:28 GMT

Since I am doing a LOAD *, I am going to bring all the fields from STATE table without changing their name it will autoconcatenate it to the state table if I don't add NOCONCATENATE. NOCONCATENATE tells QlikView to not append it to STATE and create a new table named JOINED_TABLE.

Re: Inline join syntax - need help

PradeepReddy — Fri, 05 Feb 2016 19:38:50 GMT

generally in qlikview, if two tables with identical field sets they would normally be Concatenation automatically.

The NoConcatenate prefix forces two tables with identical field sets to be treated as two separate internal tables.

Re: Inline join syntax - need help

Anonymous — Fri, 05 Feb 2016 19:54:35 GMT

OK, I just removed "NOCONCATENATE". When I ran it, I got an "Table Not Found" error. I did it as a test.

Re: Inline join syntax - need help

sunny_talwar — Fri, 05 Feb 2016 19:56:11 GMT

yup , testing is a good way to learn new things.

Re: Inline join syntax - need help

Anonymous — Fri, 05 Feb 2016 19:58:17 GMT

Does that mean it's required? Just trying to understand the responses here.

Re: Inline join syntax - need help

sunny_talwar — Fri, 05 Feb 2016 19:59:41 GMT

Yes you do, without that you will get error

Re: Inline join syntax - need help

Anonymous — Fri, 05 Feb 2016 20:07:13 GMT

OK, I guess the more I do with QV, the more i will learn.

From the earlier responses, I was interpreting it as an option. pradeep said "The NoConcatenate prefix forces two tables with identical field sets to be treated as two separate internal tables". "forces" usually implies it's an option.

You explicitly places the code there but you don't have to. In this case, it will fail if you don't have to.

Thanks!

Re: Inline join syntax - need help

sunny_talwar — Fri, 05 Feb 2016 20:12:21 GMT

I guess what he is trying to say is that you would need NOCONCATENATE for something like this:

LOAD *,

but if you add a dummy field or rename one of the fields then you won't need it. For example:

LOAD *,

1 as Dummy

The above won't need NOCONCATENATE

Re: Inline join syntax - need help

Anonymous — Fri, 05 Feb 2016 20:15:13 GMT

Got it. I interpreted incorrectly. Sorry pradeep.

Re: Inline join syntax - need help

sunny_talwar — Fri, 05 Feb 2016 20:16:57 GMT

Great, we are glad we were able to help.