CONCATENATE 2 tables after Join on Resident Table

[jchoucq]

Hello,

Is it possible to Concatenate two tables which are the result of an INNER JOIN on Resident Table?

Example :

MaTable:

LOAD
Champ1,
Champ2,
RESIDENT MaPremiereTable;
INNER JOIN LOAD Champ1, SUM(Indicateur1) AS Indicateur
RESIDENT MaDeuxiemeTable
GROUP BY Champ1;

CONCATENATE

LOAD
Champ1,
Champ2,
RESIDENT MaPremiereTable;
INNER JOIN LOAD Champ1, SUM(Indicateur2) AS Indicateur
RESIDENT MaDeuxiemeTable
GROUP BY Champ1;

I would say that the CONCATENATE Order is not necessary, but in both cases, i don't get the result i am waiting for.

The difference in the two load statement is the measure that i want to sum.

The only answer i found is to load in two different tables, and then Concatenate them ....

Thanks

Johann

[johnw]

I believe the best approach is the one you already found - load them into two different tables, then concatenate the second onto the first when you're done, then drop the second.

[jchoucq]

Thank you for your answer John.

Does it mean there is no way to do it like i try in my first example ?

Because to concatenate afterwards, i need to execute a statement like :

Load * Resident MaTable1;

and the statement does nothing if i don't create a technical column ... what a pity ...

Johann

[llauses243 · Accepted solution]

Hi,

Why difficult if we can make easy ?

1.- Tha table MaPremiereTable contains fields Champ1 & Champ2

2.- Creating the next table ...

SumIndicateurChamp1:

LOAD Champ1, SUM(Indicateur1) AS Indicateur1sum, SUM(Indicateur2) AS Indicateur2sum, SUM(Indicateur1+Indicateur2) AS IndicateurSum
RESIDENT MaDeuxiemeTable
GROUP BY Champ1;

You have automatic catenation (by columnar organization data) into tables & more facilities moreover clear.

Good luck, Luis

[jchoucq]

In fact, i really don't understand why I can't use this statement.

MaTable:

LOAD
'Table1' AS Champ1,
Champ2,
RESIDENT MaPremiereTable;
INNER JOIN LOAD Champ1, SUM(Indicateur1) AS Indicateur
RESIDENT MaDeuxiemeTable
GROUP BY Champ1;

CONCATENATE

LOAD
'Table2' AS Champ1,
Champ2,
RESIDENT MaPremiereTable;
INNER JOIN LOAD Champ1, SUM(Indicateur2) AS Indicateur
RESIDENT MaDeuxiemeTable
GROUP BY Champ1;

When I execute the script, i don't get any errors, but i don't get a the result i am waiting for. For example, i don't get, in the column Champ1, the two differents string "Table1" and "Table2" but only "Table1" !?

Johann

[johnw]

First, I think Luis has a good answer. The concatenation does seem pointless now that I'm looking at it more closely. Just put the sums in a different table as he indicated.

As for why you can't do it like you're doing it, just think about it step by step, as QlikView processes it. And let's create some sample date:

MaPremiereTable:
Champ1, Champ2
1, A
2, B

MaDeuxiemeTable:
Cham1, Indicateur1, Indicateur2
1, 1, 3
2, 2, 4

Step 1 - You build a table with fields Champ1 and Champ2.

MaTable:
Champ1, Champ2
1, A
2, B

Step 2 - You inner join to get field Indicateur

MaTable:
Champ1, Champ2, Indicateur
1, A, 1
2, B, 2

Step 3 - You concatenate the Champ1 and Champ 2 fields again:

MaTable:
Champ1, Champ2, Indicateur
1, A, 1
2, B, 2
1, A
2, B

Step 4 - You INNER join to Champ1=1,Indicateur=3 and Champ1=2,Indicateur=4. This eliminates the first two rows since they don't match the Indicateur values. Assuming I did all this right, you get only the second set of rows:

MaTable:
Champ1, Champ2, Indicateur
1, A, 3
2, B, 4

In some cases, you would get rows from the first load as well, but only where sum(Indicateur1) = sum(Indicateur2), I believe.

You seem to be thinking that QlikView will somehow process both inner joins independently. It doesn't. The last inner join affects ALL rows of the original table, not just the most-recently-concatenated rows. QlikView doesn't remember which ones you most recently concatenated, and doesn't care.

[johnw]

jchoucq wrote:Does it mean there is no way to do it like i try in my first example ?

Right.

jchoucq wrote: Because to concatenate afterwards, i need to execute a statement like :
Load * Resident MaTable1;
and the statement does nothing if i don't create a technical column ... what a pity ...

Mmmm, no, you shouldn't have to create an extra column. You just have to tell it which table to concatenate to:

CONCATENATE (MaTable)
LOAD * RESIDENT MaTable1;
DROP TABLE MaTable1;

But again, I think Luis has a better answer.

[jchoucq]

Thank you very much John and Luis. Your help has been very useful. Thanks a lot.