topic Yet another Concatenation & Join problem in QlikView

Yet another Concatenation & Join problem

Fri, 16 Jul 2010 11:56:15 GMT

I have a master products table with (simplifiyng):

Code

Description

GroupCode

and several products tables coming from different databases (i have a loop in the script), with identical structure:

Code

Company

StandardCost

I would likt to see all this as a single table, like this:

Products:

Code

Description

GroupCode

Company

StandardCost

In other words, I need Description and GroupCode taken from the master database and StandardCost taken from each single company table.
The problem is that, If I try by concatenating the master with the other tables, I get duplicated rows from each code, one with correct Description and GroupCode, and others from each other table with null values for Description and GroupCode.
On the other hand, I tried also with a JOIN in each table with the master, but in this case, I get n table labels "Product, Product-1, etc..." and I need to have a single resulting table. By the way, why I get different table labels, if the tables are identical?

I am sure there is a trivial solution I'm missing....

Thanks

Yet another Concatenation & Join problem

sparur — Fri, 16 Jul 2010 12:31:57 GMT

Hello Paolo.

You should use a JOIN statement, for example:

Products:

LOAD Code,
Description,
GroupCode

FROM master products table;

LEFT JOIN (Products)

LOAD Code,
Company,
StandardCost

FROM other table;

after this you get table with such structure:

Products:
Code
Description
GroupCode
Company
StandardCost

Yet another Concatenation & Join problem

Fri, 16 Jul 2010 13:01:07 GMT

Thanks Anatoly,

actually, i didn't find the way to do that, because I have one master table, but several company tables, like that:

- MASTER DB CONNECTION

LOAD.... FROM master products;

- COMPANY 1 DB CONNECTION
.... other stuff
LOAD .... FROM other table
.... other stuff

- COMPANY 2 DB CONNECTION
.... other stuff
LOAD .... FROM other table
.... other stuff

...etc...

In this case, the script you are suggesting is working only for the first company table.

Yet another Concatenation & Join problem

Fri, 16 Jul 2010 16:35:21 GMT

Hi Paola,

The trick is to first load the company tables first after each other. If the tables have the same field structure, they are concatenated automatically to 1 table containing the product costs for all companies. Then at the last step, you can do a right join with the table containing the products and the groups.

The code would look like this:

Products:

LOAD
Code,
Company,
StandardCost
FROM
Company1;

LOAD
Code,
Company,
StandardCost
FROM
Company2;

RIGHT JOIN
LOAD
Code,
Description,
GroupCode
FROM
Products;

However, you could also consider to keep 2 tables (1 with products and another with the company/price info). I would prefer this myself, as it avoids double product keys in the table. In the frontend, the data would look the same to an end user. I've included a zip file with both examples attached.

I hope this answers your question.

Daniel

Yet another Concatenation & Join problem

Fri, 16 Jul 2010 17:24:03 GMT

Thank you Daniel,

I was triyng also with a pre-joined SQL view across the database to load from. I will check wich is the faster solution...

Thanks