Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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.
Looks like the last join should be LEFT rather than INNER.
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"?
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".
Ok, thank you!
Michael Solomovich wrote:
Looks like the last join should be LEFT rather than INNER. <div></div>
That was also right! Thanks, Michael.