topic using a variable in load statement in QlikView

using a variable in load statement

Thu, 11 Nov 2010 17:38:48 GMT

Hi!

I've been away from the QlikView environment for a while, but I should think I can remember something as simple as this...

I start off loading a variable from a table like this...
LOAD
date(min(Run_Time)) as CoInfoRunDate
FROM CompanyInfo.xls (biff, embedded labels);
LET CoInfoDate=peek('CoInfoRunDate',0);

...then try to use it in my load statement like this...
LOAD $(CoInfoDate) as HS.Count_Date

The initial load and the variable assignment are both working perfectly, but when the table is created, the value for HS.Count_Date comes up as 12/31/1899, which I'm guessing is the date representation for zero. I'm guessing somehow the date at that point is being interpreted as a string with a value of zero but i don't know how to force it to be a date.

Ideas anyone?

Thanks,
Dino

using a variable in load statement

Miguel_Angel_Baeyens — Thu, 11 Nov 2010 17:45:38 GMT

Hello Dino,

If the variable gets in the first place the correct value, then my guess is that you want to do the following

LOAD Date($(CoInfoDate)) AS HS.Count_Date, // rest of the fields below

That will store the value of the variable in a field named "HS.Count_Date" for each of the records you load.

Hope that helps.

using a variable in load statement

Thu, 11 Nov 2010 19:09:47 GMT

Thanks Miguel, but I had already tried date() at all points with no success. What eventually did it was using date#() during the initial load, which established it as a date type early on. Everything else flowed through properly after that.

Apparently, date() only formats a value as a date, but keeps it as a string. Date#() made it a true date.

using a variable in load statement

Miguel_Angel_Baeyens — Thu, 11 Nov 2010 19:34:32 GMT

You're welcome. The thing is that in QlikView there are no datatypes as such. Any value may be either text or numeric, so are dates. The Date() function interprets a numeric value as a date. When this value is not numeric, then Date#() is the way to get it done. That's why you will see several code examples using

Date(Date#(field, 'DDMMYYYY)) AS DateField

Regards