topic Re: how to create join among 4 qvd? in QlikView

how to create join among 4 qvd?

Thu, 06 Jul 2017 00:00:48 GMT

I am trying to achieve this

//sql

select

table_1.a,

table_2.b,

table_3.c,

table_4.e

from table_1, table_2, table_3, table_4

where

table_1.p = table_2.p and

table _2.q = table _3.q and

table _3.r = table _1.r and

table_4.s = table_2.s

I know a way to achieve this qlikview using tables but I want to achieve this using qvds as the data is huge.

Re: how to create join among 4 qvd?

ankur_abhishek — Thu, 06 Jul 2017 02:45:14 GMT

Hi Urmil,

What type of join you what on those 4 tables(left, right ,outer) . & can you share the qvds also.

Re: how to create join among 4 qvd?

Anil_Babu_Samineni — Thu, 06 Jul 2017 03:02:12 GMT

When you said, You know the structure and procedure for tables. What if you tried same way for qvd? Because, QVD is nothing but simple tables only or Flat files.

Re: how to create join among 4 qvd?

prma7799 — Thu, 06 Jul 2017 04:51:35 GMT

Please share some sample QVD with at least 15-20 records.

Re: how to create join among 4 qvd?

kamal_sanguri — Thu, 06 Jul 2017 06:40:31 GMT

There is absolutely no difference in terms of using a table or data from QVD. You can simply load QVDs the way you load other tables (spreadsheet/text files etc) and use join the same way.

For ex:

QVD_1:

Load *

From (First QVD);

LEFT JOIN

Load *

From (Second QVD);

and so on.

Re: how to create join among 4 qvd?

Kushal_Chawda — Thu, 06 Jul 2017 07:03:41 GMT

Create QVDs of all tables.

Data:

LOAD Key1,

key3,

...

FROM Table1;

inner join(Data)

LOAD Key1,

Key2,

Key4,

...

FROM Table2;

inner join(Data)

LOAD Key2,

...

FROM Table3;

inner join(Data)

LOAD Key3,

...

FROM Table3;

inner join(Data)

LOAD Key4,

...

FROM Table4;

But if you want the exact join condition then share some sample data of all 4 tables with the expected output

Re: how to create join among 4 qvd?

BalaBhaskar_Qlik — Thu, 06 Jul 2017 07:26:53 GMT

May be Like this:

Region:

LOAD * INLINE

[

RegionID, RegionName

1,East

2,West

3,North

4,South

];

LEFT JOIN (Region)

RegionContact:

LOAD * INLINE

[

RegionID, ContactName

1,John

2,Roger

5,Linda

];

Left JOIN (Region)

RegionAddr:

LOAD * INLINE

[

RegionName,ContactAddr

East,ABC

west,CDE

North,EFG

South,JKL

];

Left JOIN(Region)

CountryRegion:

Load * Inline

[

RegionName,Country

East,India

west,AUS

North,UK

South,JKL

];

Re: how to create join among 4 qvd?

jonathandienst — Thu, 06 Jul 2017 09:01:57 GMT

The QV equivalent of the SQL in your question is:

Result:

LOAD a, p, r

FROM Table1.qvd (qvd);

Inner Join(Result)

LOAD b, p, q, s

FROM Table2.qvd (qvd);

Inner Join(Result)

LOAD c, q, r

FROM Table3.qvd (qvd);

Inner Join(Result)

LOAD e, s

FROM Table4.qvd (qvd);

Re: how to create join among 4 qvd?

jonathandienst — Thu, 06 Jul 2017 09:03:49 GMT

Optionally, drop the fields used for the join when done:

DROP Fields p, q, r, s;

Re: how to create join among 4 qvd?

Thu, 06 Jul 2017 16:30:37 GMT

I want simple natural join( no left or right join). Just want to retrieve those columns based on conditions I have included in where clause. I cannot share the data because the number of columns to retrieve is around 25 and each of the qvd contains around 20 columns.

I dont want to have the columns which are in 'where clause' to be loaded along with required columns to display, so

if i am using below then how do I include where clause for conditions?

Data:

LOAD Key1,

key3,

...

FROM Table1;

join(Data)

LOAD Key1,

Key2,

Key4,

...

FROM Table2;

join(Data)

LOAD Key1,

Key3,

Key4,

...

FROM Table3;

I cannot use sql select on qvd? because I can get my result using below

combined:

sql select

p."SCENARIO_NAME",

pr."PROCESS_STEP_NAME",

pd."PRODUCT_MODEL_NAME"

from

"OTD_ADMIN"."SCG_OO_PROD_PROCESS_FLOWS" p,

"OTD_ADMIN"."SCG_PROCESSES" pr,

"OTD_ADMIN"."SCG_PRODUCTS" pd

WHERE

p.PROCESS_NAME = pr.PROCESS_NAME

AND p.WORKCENTER_NAME = pr.WORKCENTER_NAME

AND p.PROCESS_STEP_NAME = pr.PROCESS_STEP_NAME

AND p.NEXT_PROCESS_STEP_NAME = pr.NEXT_PROCESS_STEP_NAME

AND p.PRODUCT_NAME = pd.PRODUCT_NAME;

I want above query for qvds thats it.

Re: how to create join among 4 qvd?

jonathandienst — Fri, 07 Jul 2017 04:50:23 GMT

OK, that's a little different to what you asked in the original post. If you have a working SQL query, then do the join in SQL. That's usually the fastest because you only need to transfer the result to QV.

Otherwise, use the script that I posted earlier.

By default an unqualified QV join (no left/right/inner) is a full outer join, so you will want to specify "inner join" to be equivalent to the SQL example. You cannot achieve the join in QV without loading the join keys - how could QV perform the join without them? You can always drop them when the joins are done like I suggested.

Re: how to create join among 4 qvd?

Fri, 07 Jul 2017 22:01:48 GMT

HI John,

thank you for your answer. I have one more problem so in the join I am trying to calculate a column based on fields from two tables like this

sql select

p.a as "some field",

(p.b * q.c) as calculation

from table_1 p,

table_2 q

where

p.t = q.r

so now in load statement (below) how do we put this join ?

DATA:

load

b from

qvd_1

inner join(DATA)

load

(b * c) as calculation

from qvd_2

like this?? and one more question Can we store a sql select join query (like above i wrote) in a qvd??

I tried this but I could not get