You can make use of any Qv date and time function (please look in the help for full details) in the LOAD part of your script.
So you could use something like
(84 other fields)
... (more date functions)
SQL SELECT ..
You might also want to consider to create a master calendar and linking this calendar to your table using CREATION_DATE. Search for master calendar here in the forum, there a lots of examples / explanations.
I don't use QV functions in the SQL query (I believe this isn't possible either).
The quere has two parts: A SQL query is used to talk to the database, here I would use restrictions to data retrieved (i.e. "where ...") if necessary.
The LOAD part is QV specific and will not affect the database transfer. Here you could use any QV commands allowed in script part.
Both parts togehter build up your table in QV (You could drop the LOAD part, but then you can't use the QV (date) functions).
In the SQL part, SELECT * or only the fields needed.
If you use a master calendar, you can stick with your simple SQL query and link your existing table with the master calendar, using the CREATION_DATE field. The Master calendar will use a LOAD ... part to create all necessary date fields then.
I think it sounds more complicated than it actually is ;-)
Excellent .. i didn't know about LOAD/SELECT
i have made my LOAD with functions and it's nice.. thanks
I have downloaded Jason Long's Master Calendar.. it seems nice but i have not managed to link my table with the master calendar.. I have put a as CREATION_DATE in the master calendar script, the link appears in the table viewer but when i use calendar objects, i have no KPIs ..
if i make a to_char(CREATION_DATE,'dd/mm/yyyy') in my SQL, QlikView will take it as a $date tag and it works fine
but if i don't transform it in the SQL, QlikView consider it as a $timestamp only and doesn't make the link.
Is there a function to make Qlikview treat it as a $date in the LOAD ? Date(CREATION_DATE) as CREATION_DATE is not enough
Date is the function to format a Date Type.
To restrict a timestamp to date part only, try
if QV has recognized the field values as timestamp.
You may also want to look at the Date parsing function
Date#() resp. Timestamp#()
Am 21.08.2011 16:16 schrieb "fredericvillemin" <
created the discussion
"Re: Best way to have Date fields ?"
To view the discussion, visit:
I had the same issue, and found that master calendars get tricky when your SQL source has multiple date fields. After a bit of hair pulling, I worked out a script which scans your SQL for date field and loads the master calendar once for each date field, prefixing the fields to avoid name collisions (the code is mostly from Witherspoon, I just adjusted it slightly). I think this works pretty well; I can add a new date field to the SQL view which is used to populate my "Raw Dates.qvd" file, and all downstream reports will pick up the new field without me having to lift a finger.
The thread is here: http://community.qlik.com/message/168562, and attached is the script. A QVW which loads from one or more fact table QVDs, and then uses this script to create date tables, looks like this (after the "SET format" boilerplate):
LOAD * FROM "\\SQLBI001\QlikView Documents\Raw Data\Raw Stores.qvd" (qvd); LOAD * FROM "\\SQLBI001\QlikView Documents\Raw Data\Raw Orders.qvd" (qvd); $(Include=Load Dates.qvs);
Very compact, and very simple, which is helpful if you have non-technical users building reports.
Load Dates.qvs 3.3 K