how to create join among 4 qvd?

[#1]

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.

[ankur_abhishek]

Hi Urmil,

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

[Anil_Babu_Samineni]

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.

[prma7799]

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

[kamal_sanguri]

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.

[Kushal_Chawda]

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

[balabhaskarqlik]

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

];

[jonathandienst · Accepted solution]

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);

[jonathandienst]

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

DROP Fields p, q, r, s;

[#10]

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.