even if you reformat a field like Date(Datefield, .dd/mmm/yyyyy') as Date.
the internal representation is unchanged, so if your field encodes Time in decimal places, it will still do so.
Most of your values won't match if you try to link a calendar with pure date values to a field with timestamp values (integer to float values).
So you need to transform your field to pure date representation, not only reformat the string representation.
Using floor() could do the job, or use daystart() to use one of the date/time functions.
You said this worked only partially, and you had problems with current year. Could you describe this a bit more?
It might be a good idea to look at the numerical representation of your calendar field and your fact table field to see what the mismatch causes.
Thanks I know understand about how the format did not change the internal
set up. It makes perfect sense.
I like your daystart suggestion, so in this case I think it would be:
I went back and spotted the villian. I originally started with the live db
to get going and then switched to the test db. I thought the test db was a
recent copy. It is not and does not have 2012 data. So floor works. A d'oh
So I am onto the next problem how to restrict data by date and another data
value which is a character field (ie text) in the load.
Do you have any suggestions or helpful hints?
On 10 Apr 2012 23:59, "swuehl" <firstname.lastname@example.org
Have you looked into WHERE clause?
You can look e.g. into the Incremental load sample in the QV cookbook (http://robwunderlich.com/downloads/)
for an example of the usage and syntax.
It shouldn't be a problem to use a where clause also with a character field.
If you could post more details about your load, someone might help you with the syntax.
Hope this helps,
Again thanks, I have tried the Where clause but not succesfully. Here
are some examples:
//************attempt to limit load for dates after 2011***********
//where num(date(ModifiedOn)) > 2011;
//Where (ModifiedOn)>=2010 and (ModifiedOn)<=2011;
//WHERE Date >= $(vDateMin) AND Date <= $(vDateMax);
//************attempt to limit load by field with specific character
//where (subject) = 'Paid*';
Below is the load statement.
// Date(ModifiedOn, 'DD/MMM/YYYY') as Date,
floor(ModifiedOn) as Date,
1 as CallOutcomeCount,
SQL SELECT *
If you are using WHERE in an SQL SELECT statement, you can only use statements that are supported by your SQL connector. You cannot use all QV functions. So you need to check the correct SQL syntax for your date restriction.
Some general comments (e.g. if you use where in a QV load, e.g. a resident load): A date is number (with zero point 1899-12-30), if you want to get the year of that date / number, you need to use year() function.
If you want to compare dates (e.g. Date >= $(vMinDate) ), most issues arise from problems with differing date formats, so assure that the input date format matches your value you want to compare against.
If you want to restrict by comparing to character, you need to use a QV operator like LIKE or a function like wildmatch() if your value contains wildcards.