Sum column during load from two QVDs

[richnorris]

Hi, I'm trying to get the total volume for all clients in a given country, but I need to have it stored seperately from the individual volume records because these will get reduced by the visibility, but in order to work out volumes as a percentage I still need to have the total for a country available. The client information is stored in a seperate QVD to the trade information, so I need to first of all store the trade information, then join it to the client information, and do a group by country sum. My code at the moment looks like the following:

Client:
LOAD CLIENT_ID as [Sales Client ID_],
Client.COUNTRY,
FROM Client.qvd (qvd);

Join (Client)
LOAD [$ Volume],
[Sales Client ID_]
FROM CT_FX0.QVD] (qvd);

Join (Client)
LOAD [$ Volume],
[Sales Client ID_]
FROM CT_CEX.QVD (qvd);

load
[Sales Client Country],
sum([$ Volume]) AS CountryTotal
RESIDENT Client
Group By [Sales Client Country];

This works fine when I only load trade data from one QVD, but when I load it from both, I get lots of null countries atributed to whichever loads second. How do I get round this? Is there a neat way of loading trade information from lots of different QVDs?

[syed_muzammil]

Hi,

Use a left join instead of a simple join. This way you will get the country for only those clients present in the client table.

Regards,

Syed.

[rwunderlich]

The first Join will add the field [$ Volume]. That field will then become a matching field in the second join, which I'm sure is undesirable.

Instead load both volume files first and then join.

Volume:
LOAD * FROM CT_FX0.QVD] (qvd);
LOAD * FROM CT_CEX.QVD (qvd);

JOIN (Client) LOAD * RESIDENT Volume;
DROP TABLE Volume;

-Rob