Datapull from multiple qvds

[aarohipatel]

Hello,

I have 3 QVD' s that I have loaded for 3 separate applications. 

Now, I want to use those 3 qvd's in a new application so that I can see all the data in one single application instead of 3 different applications.

PREM.QVD has the following fields:

1. KEY

2. ACCOUNT_NAME

3.ACCOUNT_ADDRESS

4.TECHNICIAN_NAME

5.TECHNICIAN_ADDRESS

6.NUMBER

7. TERM

8. MONTHLY_PREMIUM

INF.QVD has the following fields:

1. KEY

2. ACCOUNT_NAME

3.ACCOUNT_ADDRESS

4.TECHNICIAN_NAME

5.TECHNICIAN_ADDRESS

6. NUM

7. TERM

8. INF_PREMIUM

RET.QVD has the following fields:

1. KEY

2. ACCOUNT_NAME

3.ACCOUNT_ADDRESS

4.TECHNICIAN_NAME

5.TECHNICIAN_ADDRESS

6. NUM

7. TERM

8. RET_NUM_AMT

9. RET_DEN_AMT

I want to do a full outer join and pull all the fields PREM.QVD

and 'INF_PREMIUM' from INF.qvd

and 'RET_NUM_AMT', 'RET_DEN_AMT' from RET.qvd

Please advise on how to do a full outer join?

Thank you in advance.

[dplr-rn]

Outer join is the default join in qlik. use something like below 

YOURTABLENAME:
load *
from PREM.QVD

join(YOURTABLENAME)
load
key,
NF_PREMIUM
from INF.qvd;

join(YOURTABLENAME)
load
key,
,RET_NUM_AMT
, RET_DEN_AMT 
from RET.qvd;

check below links

https://help.qlik.com/en-US/sense/February2019/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPref...

https://help.qlik.com/en-US/sense/February2019/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPref...

https://community.qlik.com/t5/QlikView-Creating-Analytics/Understanding-Join-Keep-and-Concatenate/td...

[Vegar]

The syntax for full outer join is JOIN.

In QlikView you will join on all fields that have the identical name. So in you do the following...

Data:

Load * from PREM.QVD (qvd);

Join (Data)

Load * from INF.QVD;

...you will join on all the following fields

1. KEY

2. ACCOUNT_NAME

3.ACCOUNT_ADDRESS

4.TECHNICIAN_NAME

5.TECHNICIAN_ADDRESS

7. TERM

Note that you will not join on field 6 as they have different names in the two tables, NUMBER and NUM. To even include the NUMBER in the join you should rename it in one of the table (NUM as NUMBER)

[aarohipatel]

Hi Dilipranjith,

I did try that but the reload failed after loading PREM and INF. Failed to load RET 

When looked at the application log, no specific error mentioned except the following:

2019-04-08 10:32:25 Joining/Keeping
2019-04-08 10:34:55 -129 =
2019-04-08 10:34:57 Execution Failed

Please advise

Thanks

[dplr-rn · Accepted solution]

i have an extra comma by mistake in previous one

join(YOURTABLENAME)
load
key
,RET_NUM_AMT
, RET_DEN_AMT
from RET.qvd;

[Vegar]

By the look of your field names it seems like the fields you are adding with join are all numeric fields used for calculations and not dimensions used for selections in the application.

If so, then I would suggest you to consider to concatenate the QVDs as an good alternative to implementing a double full outer join.

TABLENAME:
LOAD * FROM INF.QVD (QVD);

CONCATENATE (TABLENAME)
LOAD * FROM PREM.QVD (QVD);

CONCATENATE (TABLENAME)
LOAD * FROM RET.QVD (QVD);

[aarohipatel]

It still gives the same error in my script .

How about the following:

PREM:

Load
KEY,
ACCOUNT_NAME,
ACCOUNT_ADDRESS,
TECHNICIAN_NAME,
TECHNICIAN_ADDRESS,
NUM,
TERM,
MONTHLY_PREMIUM
from PREM.QVD

Outer Join(PREM)
INF:
Load
KEY,
INF_PREMIUM
from INF.QVD

Outer Join (PREM) //question: should this join be (PREM) or (INF)
RET:
Load
KEY,
RET_NUM_AMT,
RET_DEN_AMT
from RET.QVD

Thanks!

[aarohipatel]

Hi Vegar,

The fields that I have mentioned are just a few of them from my script.

I have around 90 dimension fields which are common in all the 3 qvds

10 amount fields in each of the qvd's. Which I am using in expressions.

If I have to join on all the dimension fields available , it would cause performance issue. Thats why I would like to join based on the 'KEY' field only.

Thanks !

[dplr-rn]

Do you have semi colons after the loads?

PREM:

Load
KEY,
ACCOUNT_NAME,
ACCOUNT_ADDRESS,
TECHNICIAN_NAME,
TECHNICIAN_ADDRESS,
NUM,
TERM,
MONTHLY_PREMIUM
from PREM.QVD
;
Outer Join(PREM)
Load
KEY,
INF_PREMIUM
from INF.QVD
;
Outer Join (PREM)
Load
KEY,
RET_NUM_AMT,
RET_DEN_AMT
from RET.QVD;

[Vegar]

Then a join seems like a good approach. Make sure that your Key is unique per row so you don't get duplicated values in your join.

A comment on your last script posting, you are joining on PREM and it looks like you are missing semicolons between your loads.