topic Re: Joining multiple qvd problem in QlikView

Joining multiple qvd problem

Tue, 08 Oct 2013 15:18:47 GMT

Hello everyone,

I have the following scenario in my application:

I have weekly generated qvd’s with the same structure, named AVA_WEEK_CHAN_KPI* and weekly generated qvd’s with the same structure, named DNS_KPI_* (* stands for week number and country , in both cases).

I have to make an inner join on data from all the qvd AVA_WEEK_CHAN_KPI* with data from DNS_KPI_* .

What is strange for me is that, when I have 1 qvd AVA_WEEK_CHAN_KPI* and 1 qvd DNS_KPI_* it works well, there are common records, but when there are more than 1 qvd’s of same type, no common records can be found.

I did some tests on data and I can assure that data in the qvds are good; join returns common records when there is a single qvd for every of the used weeks(44,45,46)

Can you give me a suggestion or a hint?

I attached a small application illustrating this issue.

Re: Joining multiple qvd problem

jvitantonio — Tue, 08 Oct 2013 15:40:45 GMT

Hi, please find the solution attached.

You need to create a composite key otherwise QV will create a synthetic one and we want to avoid those.

Re: Joining multiple qvd problem

Thu, 10 Oct 2013 09:51:07 GMT

Hello, thank you for your answer, but the app returns a carthesian product instead of an inner join.

If you check my attachment, there were not synthetic keys so I'm not sure that this is the cause.

As I said below, when I have a qvd of each type, works fine, but when I have multiple qvd's, no results are displayed.

Re: Joining multiple qvd problem

Gysbert_Wassenaar — Thu, 10 Oct 2013 10:42:27 GMT

After the first inner join only the records with common keys with the first DNS_* qvd remain. Then another inner join with the second DNS_ qvd is done. And if there are no common keys between the first and second DNS_ qvd then you have no records left. What you want to do is first load all the qvd's without the inner join. Then finally do an inner join between the two tables using a resident load. Simplified:

TempAVA

load * from AVA*.qvd (qvd);

TemDNS:

load * from DNS*.qvd (qvd);

inner join(TempAVA)

load * resident TempDNS;

drop table TempDNS;

Re: Re: Joining multiple qvd problem

jvitantonio — Thu, 10 Oct 2013 11:42:37 GMT

Hi, try now.