topic SQL WHERE clause based on loaded data in QlikView

SQL WHERE clause based on loaded data

Fri, 02 Oct 2009 22:21:27 GMT

Hi,

I'm not sure if this is possible in QlikView, but am hoping it is. I am loading a qvd file, then loading the latest data from SQL, finally storing the data back to the qvd file ready for the next day.

At the moment I load one day's data on each load. However if the QlikView file is not ran that day, the day is missing, and if it gets ran twice then the day is duplicated.

What I need is something like this:-

DataTable:

LOAD CalendarDate, FileSize
FROM C:\File.QVD (qvd);

CONCATENATE SQL SELECT CalendarDate, FileSize
FROM DW.dbo.vwFileSizes
WHERE calendardate > #$(max(CalendarDate))#;

STORE DataTable INTO C:\File.QVD;

The bit that does not work is the #$(max(CalendarDate))# bit. I want this to be the maximum date from the qvd load.

Any ideas?

SQL WHERE clause based on loaded data

Oleg_Troyansky — Fri, 02 Oct 2009 23:11:57 GMT

you are almost there. You need a couple of extra steps:

1. Load from QVD

2. Calculate max(Date) using resident load and group by

3. Use function peek() to assign the value to a variable vMaxDate

4. Use $-sign expansion to pass the value to SQL:

WHERE calendardate > $(vMaxDate)

cheers,

SQL WHERE clause based on loaded data

johnw — Fri, 02 Oct 2009 23:22:37 GMT

I'll point out a potential pitfall - you might easily have a new file with the same date as a file already in the QVD. For example, you create a file, run the reload, create another file, and rerun the reload. You need to pick up the new file, not skip it just because it has the same date. To solve it, I would want to grab the max date records from the database instead of from the QVD. It's not as simple as just using ">=" of course, because that will double up on the max date records. How I would solve it depends on how much data you're expecting, though.

SQL WHERE clause based on loaded data

Fri, 02 Oct 2009 23:51:15 GMT

Thanks for the reply Oleg. It looks better, but is still not right. Below is the code I now have.

When I run this I get an error, the vMaxDate passes through "peek(MDate,0,DataTable2)", rather than the actual date.

Once this works the current table is DataTable2, how do I concatenate the new data to DataTable?

DataTable:

LOAD

CalendarDate

FileSize

FROM

C:\File.QVD (qvd)

;

DataTable2:

load

max(CalendarDate) as MDate resident

DataTable;

set

vMaxDate=peek(MDate,0,DataTable2)

;

concatenate

SQL SELECT CalendarDate,

FileSize

FROM

DW.dbo.vwFileSizes

where

calendardate > $(vMaxDate)

;

SQL WHERE clause based on loaded data

johnw — Sat, 03 Oct 2009 00:32:52 GMT

I'm not certain, but I THINK you need to use LET instead of SET, and I think you need single quotes around MDate:

LET vMaxDate=peek('MDate');

SQL WHERE clause based on loaded data

rwunderlich — Sat, 03 Oct 2009 06:57:35 GMT

You can get a working example of a date based incremental load from the QV Cookbook at:

http://robwunderlich.com/Download.html

The specific example is titled "Incremental reload template and example."

-Rob

SQL WHERE clause based on loaded data

Mon, 05 Oct 2009 16:47:32 GMT

Thanks John,

It's all working now. I've added the code below in case anyone else has the same issue. This also shows how to concatenate to an existing table other than the current table in use.

Thanks for all your help.

DataTable:

LOAD CalendarDate,

FileSize

FROM C:\File.QVD (qvd);

DataTable2:

load max(CalendarDate) as MDate resident DataTable;

let vMaxDate=peek('MDate');

concatenate (DataTable) SQL SELECT CalendarDate, FileSize

FROM DW.dbo.vwFileSizes

where calendardate > $(vMaxDate);

drop table DataTable2;