Best way to have Date fields ?

[fredericvillemi]

Hi,

i'm trying to find the best way to have fast and useful date fields.

My script contains only one SQL query which take 88 fields for 500 000 lines

SQL SELECT ID,

    USERID,

    SITEID,

... (84 other fields)

     CREATION_DATE;

The Creation_date is in a date format but i would like to make listboxes and dimensions with this date in several other forms : week / month / day / quarter

What is the best way to do that ?

1 - adding conversion in the query : to_char(CREATION_DATE, 'yyyy/mm') as month

It will be really fast in Qlikview but will need 500000*7 chars of network transfer to get the data from database. If we think that there are several dates in my query and i want 4 conversions for each one, it will make a longer and bigger query transfer.

2 - adding calculated fields in Qlikview : really slow. I have tried to make a listbox with year(CREATION_DATE) and it's really really slow. I can't keep that solution.

3 - is it possible to add in the script the creation of a qlikview "table" in which i would declare for each ID the different conversion of CREATION_DATE. This way, Qlikview would only calculate all conversion once at script reloading and it would be really fast without too much network.

Can i make solution 3 and how ?  or do you have any faster solution ?

Thanks

[swuehl]

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

LOAD

ID,

SITEID,

(84 other fields)

CREATION_DATE,

month(CREATION_DATE),

year(CREATION_DATE),

... (more date functions)

;

SQL SELECT ..

from Table;

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.

Regards,

Stefan

[fredericvillemi]

i'm going to look for Master Calendar thanks

but in your example, Qlikview's functions used in the sql query will be translated to SQL isn't it ?

so it will create bigger network transfer from the database.. or maybe qlikview's functions are created locally only ?

[swuehl · Accepted solution]

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 😉

Regards,

Stefan

[fredericvillemi]

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 ..

[swuehl]

Check that your master calendar really spans the period you have data in.

You can use a plain list box with a Master Calendar Date to select a time range and check the effect on selected Order Dates.

[fredericvillemi]

The fact that in Oracle the field is a date/time may affect to use of Master Calendar or is Qlikview able to link a date only with a date time ?    

[fredericvillemi]

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

thanks

[swuehl]

Date is the function to format a Date Type.

To restrict a timestamp to date part only, try

Date(daystart(CREATION_DATE))

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" <

qliktech@sgaur.hosted.jivesoftware.com>:

fredericvillemin

created the discussion

"Re: Best way to have Date fields ?"

To view the discussion, visit:

http://community.qlik.com/message/142567#142567

[#10]

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.