Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey, brand new to QV and trying to do something I would have though was absolutely basic but after 2 hours still no luck.
I've imported data from a QVD in the format below. The date format is the same as the source YYYY-MM-DD. We've imported from source into QV and exported into QVD so this is definitely a date field, confirmed by the fact that it can be sorted by date and I can change the format to other date styles when I list the results in a List Box .
Results | Date |
5000 | 2015-08-20 |
10000 | 2015-08-19 |
7000 | 2015-08-24 |
6000 | 2015-08-25 |
I am trying to export part of the code into a new QVD file.
This works :
YHMN:
Load *
where Date > '2015-08-20';
However I need it to work from a variable used throughout the overall script. For annoying reasons the variable needs to give the date in format YYYYMMDD as this is how it needs to be to extract the data initially.
let vMinDate > Date(Today()-7,'YYYYMMDD');
As soon as I try to use the variable in the Load function it doesn't work. the script run but pulls in no data.
I've tried
Load * where Date > $(vMinDate)
and
Load * where Date > '$(vMinDate)'
and
Load * where Date > date($(vMinDate),'YYYY-MM-DD')
and
Load * where date(Date,'YYYYMMDD') > '$(vMinDate)'
and all sorts of variations in between. It's like it won't match a date format 'YYYYMMDD" with 'YYYY-MM-DD" despite the fact that they are both dates.
I've tried to create a second variable
let vMinDate2 = Date($(vMinDate),'YYYY-MM-DD') but this creates a date of 57071-01-24
I could of course just create a second variable with the same syntax :
Let vMinDate2 = Date(Today()-2,'YYYYMMDD')
I've tested and this works, but I want to do this dynamically. I'd rather just change one variable than 2 as in the greater script this would mean changing 10 variables every time we want to re-run the data.
Surely there is a way to make QV realise these are both dates and ignore the format. Or as a secondary option, surely there is a way to create a second variable that simply takes the first variable and changes the date format.
Any help greatly appreciated 🙂
Cheers,
Dave
In general it's recommended to use pure numeric variables for this - it avoid all the formating-trouble:
let vMinDate = num(Today()-7);
and then:
Load * where num(Date) > $(vMinDate) // will Date not be regocnized as date you need num(date#(Date, Format))
For more information see also: How to use - Master-Calendar and Date-Values
- Marcus
In general it's recommended to use pure numeric variables for this - it avoid all the formating-trouble:
let vMinDate = num(Today()-7);
and then:
Load * where num(Date) > $(vMinDate) // will Date not be regocnized as date you need num(date#(Date, Format))
For more information see also: How to use - Master-Calendar and Date-Values
- Marcus
Champion, worked perfectly.