topic Load data depending on date in QlikView

Load data depending on date

Fri, 23 Dec 2011 10:14:37 GMT

Hi All,

I'm new to Qlikview, I've had my training but I have a few more questions. I apologise in advance if this is not the place to be asking questions.

I do not want to load all data, I only want to load data between two different date values.

// Import STUDY table

STUDY:
LOAD STUDYID,
VISITID,
RISID,
EXAMTYPEID,
EXAMID,
1 as STUDYCOUNTER,
STUDYDATE,
STATUS,
STUDYUID,
BODYSITEMODIFIER,
PRIORITY,
INTERFACEID;
SQL SELECT *
FROM SolitonServer.dbo.STUDY;

I'd like to change this statement to be something like 'FROM Soliton Server.dbo.STUDY where STUDYDATE > '12/10/2010 00:00:00' and STUDYDATE < '21/01/2011 00:00:00';

I get an error when I try to use this statment. The error is below:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Any help on how to fix this error would be muchly appreciated.

Regards,

Dan

Load data depending on date

Sokkorn — Fri, 23 Dec 2011 10:31:17 GMT

Hi Dan,

Can you check data type for the field STUDYDATE. Does it is DateTime or Char? What is the formate for this field?

or you can try where STUDYDATE > '12-10-2010' and STUDYDATE < '21-01-2011'

Regards,

Sokkorn Cheav

Load data depending on date

Fri, 23 Dec 2011 10:36:51 GMT

Hi Sokkorn Cheav,

In my SQL table the Data Type of the field STUDYDATE is 'datetime'. So the format is DD/MM/YYYY HH:MM:SS.

Regards,

Dan

Load data depending on date

Fri, 23 Dec 2011 12:48:32 GMT

Hi All,

I've had some success now. Not sure what I was doing wrong but the following line seems to work fine now:

FROM SolitonServer.dbo.STUDY where STUDYDATE > '12/11/2003 00:00:00' and STUDYDATE < '01/01/2011 00:00:00';

Now I want to change these date values to be read from an xml file. I have the following script for a 'dates' table:

// Start of [daterange.xml] LOAD statements
dates:
LOAD startdate
enddate
FROM [..\..\daterange.xml] (XmlSimple, Table is [dates]);
// End of [daterange.xml] LOAD statements

So I thought I would be able to change my statement in the STUDY table to read:

FROM SolitonServer.dbo.STUDY where STUDYDATE > startdate and STUDY.STUDYDATE < enddate;

But I get the following error:

Invalid column name 'enddate'.

Any Ideas?

Thanks,

Dan

Re: Load data depending on date

Sokkorn — Sat, 24 Dec 2011 00:30:45 GMT

Hi Dan,

I think you forgot comma sign (,)

Look like this:

// Start of [daterange.xml] LOAD statements

dates:

LOAD

startdate, //Maybe this point (,)

enddate

FROM [..\..\daterange.xml] (XmlSimple, Table is [dates]);

// End of [daterange.xml] LOAD statements

Regards,

Sokkorn Cheav

Re: Load data depending on date

johnw — Tue, 27 Dec 2011 21:06:00 GMT

One load statement can't refer to field names in completely different table. So after loading the dates, I believe you need to do this:

LET vStartDate = peek('startdate');
LET vEndDate = peek('enddate');

...

FROM SolitonServer.dbo.STUDY
WHERE STUDYDATE > '$(vStartDate)'
AND STUDYDATE < '$(vEndDate)';

It is possible you will need to fiddle with the variables to get the correct format to match the studydate, but I'm thinking you won't have to as long as your XML source has them looking correct.

Load data depending on date

pover — Tue, 27 Dec 2011 21:24:10 GMT

Going back to the out of range datetime value, are you sure the format is DD/MM/YYYY HH:MM:SS and not MM/DD/YYYY HH:MM:SS since you got the error when you used '21/01/2011 00:00:00' and not when you used '01/01/2011 00:00:00'

Re: Load data depending on date

Wed, 28 Dec 2011 09:05:49 GMT

Thanks John and Karl,

John, your solution with setting the dates as variables worked great.

Karl, you were right about the date format, I think I was getting confused as the date was being displayed in DD/MM/YYYY in Qlikview but I assume that Qlikview was changing the format for me.

Problem Solved, thanks again!

Dan