incremental load on pk field with no date field?

[#1]

I have a table called timesheet(timesheet_id,payroll_id,processed_date,.....) which is connected with another payroll table (payroll_id,status,total_hours,......) through payroll id.

The timesheet table is incrementally loaded using processed_date. Now I want to load the payroll table which has only unique payroll_id but no modification date. Can we use the timesheet table to do incremental loading on payroll table?

Also If I want to load this payroll table based on unique column payroll_id (its name is id in database and i have renamed it as payroll_id in Qlikview), how can we use load and sql select to use the 'where not exists' clause. The below SQL select part has error in where not exists but still I believe the syntax should be something like this.

for example,

payroll:

load

payroll_id,

status,

total_hourse

from

payroll.qvd(qvd);

concatenate(payroll)

sql select id as payroll_id,

               status,

               total_hours

from dbo.payroll

where not exists(payroll_id);

[Jason_Michaelides]

Untested, but try:

payroll:

load

payroll_id,

status,

total_hourse

from

payroll.qvd(qvd);

concatenate(payroll)

LOAD *

where not exists(payroll_id);

sql select id as payroll_id,

               status,

               total_hours

from dbo.payroll;

[#3]

Hi Jason,

Thanks for the reply. I have one confusion there though. Basically, I have two questions here

1) Will the SQL select first load all the records from database, and then search for the unique ids whether they exists or not? .

2) Is such loading slower than reloading the full table everytime|?

Br

Arif

[Jason_Michaelides]

Well I guess it needs to check every record in the SQL table against the resident table. Whether this is done line by line as they are loaded or once the whole table is loaded I don't know. As for speed comparisons - every scenario is a bit different, so test it!

[#5]

What are the other possible solutions. Is using modification date the only way out, if the table is too big?

Br

Arif

[rwunderlich]

Does the payroll table only get inserts then, no updates?

What DBMS is this, SQL Server?

-Rob

[d_pranskus]

Hi

I would do that way, that first I select rows from timesheet table directly from sql limiting them by date. Something like this:

timesheet:

LOAD *;

SQL SELECT *

FROM timesheet

WHERE processed_date BETWEEN '$(v_date_min)' AND '$(v_date_max)';

Then I will select rows from payroll table using join:

payroll:

LOAD * WHERE EXISTS(payroll_id);

SQL SELECT p.*, t.processed_date

FROM payroll p

INNER JOIN timesheet t

ON t.payroll_id = p.payroll_id

WHERE p.processed_date BETWEEN '$(v_date_min)' AND '$(v_date_max)';

The EXISTS part in second LOAD statement is necessary to prevent loading payrolls users might have entered between two queries.

v_date_min and v_date_max are variables.

Hope this helps.

Cheers Darius