topic Re: Datapull from multiple qvds in QlikView

Datapull from multiple qvds

aarohipatel — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Datapull from multiple qvds

dplr-rn — Mon, 08 Apr 2019 18:52:24 GMT

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/ScriptPrefixes/Outer.htm

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

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

Re: Datapull from multiple qvds

Vegar — Mon, 08 Apr 2019 19:00:51 GMT

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)

Re: Datapull from multiple qvds

aarohipatel — Mon, 08 Apr 2019 19:05:43 GMT

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

Re: Datapull from multiple qvds

dplr-rn — Mon, 08 Apr 2019 19:07:56 GMT

i have an extra comma by mistake in previous one

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

Re: Datapull from multiple qvds

Vegar — Mon, 08 Apr 2019 19:16:40 GMT

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

Re: Datapull from multiple qvds

aarohipatel — Mon, 08 Apr 2019 19:16:58 GMT

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!

Re: Datapull from multiple qvds

aarohipatel — Mon, 08 Apr 2019 19:20:09 GMT

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 !

Re: Datapull from multiple qvds

dplr-rn — Mon, 08 Apr 2019 19:22:43 GMT

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;

Re: Datapull from multiple qvds

Vegar — Mon, 08 Apr 2019 19:26:36 GMT

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.

Re: Datapull from multiple qvds

aarohipatel — Tue, 09 Apr 2019 23:28:03 GMT

Yes