Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Load data depending on date

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
1 Solution

Accepted Solutions
johnw
Champion III
Champion III

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.

View solution in original post

7 Replies
Sokkorn
Master
Master

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

Not applicable
Author

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

Not applicable
Author

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

Sokkorn
Master
Master

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

johnw
Champion III
Champion III

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.

pover
Luminary Alumni
Luminary Alumni

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'

Not applicable
Author

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