Joining multiple qvd problem

[#1]

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.

[jvitantonio]

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.

J.

[#3]

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.

[Gysbert_Wassenaar · Accepted solution]

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;

[jvitantonio]

Hi, try now.