topic SQL Join or LOAD Join in QlikView

SQL Join or LOAD Join

Tue, 05 May 2009 20:18:57 GMT

Hi All,

Kind of confused with JOIN thing. Maybe someone can advise?

I have a table with 6 fields (contract, task, user, date, status, hours) . Firstly, I need to distinctivelly select all rows with minimal date, so it goes:

1. SQL SELECT contract, task, user, MIN (date) FROM table1

Now I need to add 2 more fields - I want to join my temp_table with table 1, so it will be ???

1. SQL SELECT contract, task, user, MIN (date) FROM table1

2. JOIN SELECT contract, task, user, date, status, hours FROM table1

Or am I reall confused with JOIN syntaxis?

Best regards,

Mikhail

SQL Join or LOAD Join

Tue, 05 May 2009 20:19:35 GMT

Allmost forgot - maybe I shall use JOIN LOAD function? Please, advise )

SQL Join or LOAD Join

Tue, 05 May 2009 20:57:57 GMT

I recommend you find the meaning of a join, with their respective roles, left, inner, and so on.
After this study can clearly resolve your concerns.

I recommend to catch a single database, so we recommend a one-time charge in the table, then you can do this with a call to load the resident, as often as required, then you can delete if not ultilizas with a drop table.
Good luck.
JCM

SQL Join or LOAD Join

Anonymous — Tue, 05 May 2009 21:29:37 GMT

Mikhail,
To get the right answer you have to identify the problem first. I don't thnk that your pupose is just to use join. My assumption (may be wrong) that you want to get the data from the table1 only for the earliest date for each user/task/contract combination.
If the assumption is correct, It may be something like this:


Table_src:
SQL SELECT
 contract, task, user, date, status, hours
FROM table1;
Table:
LOAD
 contract, task, user,
 MIN (date) as MinDate
RESIDENT Table_src
GROUP BY contract, task, user;
LEFT JOIN (Table) LOAD
 date as MinDate,
 contract, task, user, date, status, hours
RESIDENT Table_src;
Drop table Table_src;
Drop Field MinDate;

(There could be better ways, I think...)

SQL Join or LOAD Join

rwunderlich — Tue, 05 May 2009 23:48:37 GMT

Does your DBMS support correlated subqueries? How about doing it al lin the SELECT like this:

SELECT contract, task, user, date, status, hours
FROM table1 X
WHERE date =
 (SELECT MIN(date)
 FROM table1
 WHERE contract = X.contract AND task = X.task)

-Rob

SQL Join or LOAD Join

Wed, 06 May 2009 16:11:15 GMT

Thanks, Michael. Your suggestion was totally right )