Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
Not applicable

Re: Load data depending on date

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.

7 Replies
Sokkorn
Not applicable

Load data depending on date

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

Load data depending on date

Hi Sokkorn Cheav,

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

Regards,

Dan

Not applicable

Load data depending on date

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
Not applicable

Re: Load data depending on date

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
Not applicable

Re: Load data depending on date

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
Not applicable

Load data depending on date

Going back to the out of range datetime value, are you sure the format is DD/MM/YYYY HH:MMSmiley FrustratedS and not MM/DD/YYYY HH:MMSmiley FrustratedS 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

Re: Load data depending on date

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