topic Concatenate three tables in QlikView

Concatenate three tables

Tue, 11 Jan 2011 13:47:32 GMT

Hi,

I want to concatenate three tables. First I concatenated two of my tables.

ToDos:
SQL SELECT
"No_",
Date,
"Contact No_",
Description
FROM "To-do" WHERE Status = 2;

CONCATENATE (ToDos) SQL SELECT
Name,
"Company No_",
"Responsibility Center",
"Characteristic A",
"Characteristic B"
FROM Contact
INNER JOIN "To-do" ON (Contact."No_" = "To-do"."Contact No_")
WHERE ("To-do".Status = 2);

This seems to work, but when I (right) click on Preview in table structure overview I see none of the joined columns is filled. Any suggestion?

How would I add a third table where a Description to "Characteristic A" of Contact is stored?

Concatenate three tables

johnw — Wed, 12 Jan 2011 01:27:46 GMT

Sounds like you're after joins instead of concatenation.

ToDos:
LOAD
"No_" as "To Do"
,"Date"
,"Contact No_" as "Contact"
,"Description"
;
SQL SELECT
"No_",
,"Date"
,"Contact No_"
, Description
FROM "To-do"
WHERE Status = 2
;

INNER JOIN (ToDos)
LOAD
"No_" as "Contact"
,"Name"
,"Company No_" AS "Company"
,"Responsibility Center"
,"Characteristic A"
,"Characteristic B"
;
SQL SELECT
"No_"
,"Name"
,"Company No_"
,"Responsibility Center"
,"Characteristic A"
,"Characteristic B"
FROM Contact
;
INNER JOIN (ToDos)
LOAD
"Characteristic" as "Characteristic A"
,"Characteristic Description" as "Characteristic A Description"
;
SQL SELECT
"Characteristic"
,"Characteristic Description"
FROM Characteristics
;

Alternatively, you can do the joins and renaming in the SQL. I don't, because instead I dump fairly raw tables to QVDs, and I'm doing the joins on loads from QVDs in the actual application.

Concatenate three tables

Anonymous — Wed, 12 Jan 2011 03:31:42 GMT

Looks like the last join should be LEFT rather than INNER.

AW:Re: Concatenate three tables

Wed, 12 Jan 2011 09:59:18 GMT

Thanks for your answer. It seems to work.

But I've got a question regarding understanding. If I do it like this

ToDos:
SQL SELECT
"No_" as "To Do"
,"Date"
,"Contact No_" as "Contact"
,"Description"
FROM "To-do"
WHERE Status = 2
;
INNER JOIN (ToDos)
LOAD
"No_" as "Contact"
,"Name"
,"Company No_" AS "Company"
,"Responsibility Center"
,"Characteristic A"
,"Characteristic B"
;
SQL SELECT
"No_"
,"Name"
,"Company No_"
,"Responsibility Center"
,"Characteristic A"
,"Characteristic B"
FROM Contact
;

Where do I say that I only want those records from Contact that match with the given Contact No. from To-Do? Is it because of the same field name defined by "as"?

AW:Re: Concatenate three tables

johnw — Wed, 12 Jan 2011 18:46:03 GMT


einsTeIn.NET wrote:Where do I say that I only want those records from Contact that match with the given Contact No. from To-Do? Is it because of the same field name defined by "as"?

Yes, QlikView joins by matching field names. So that will inner join on what we've renamed to "Contact".

AW:Re: AW:Re: Concatenate three tables

Thu, 13 Jan 2011 08:39:27 GMT

Ok, thank you!

AW:Re: Concatenate three tables

Thu, 13 Jan 2011 11:19:07 GMT


Michael Solomovich wrote:
Looks like the last join should be LEFT rather than INNER. <div></div>

That was also right! Thanks, Michael.