topic Create a Date field in QlikView

Create a Date field

Mon, 11 Jul 2011 08:49:05 GMT

Hi guys!!!

I need some help...

I have three Qvds.By concatenating them I am geting a single table.Month and Year fields are present...but there is no date field (Day field).Data is geting uploaded on regular basis.I want to add a date field so that the user can see the difference between toady's data nd yesterday's data.

Create a Date field

kaushiknsolanki — Mon, 11 Jul 2011 08:55:09 GMT

Hi,

Can you please share the script file.

Regards,

Kaushik Solanki

Re: Create a Date field

Mon, 11 Jul 2011 09:16:47 GMT

Here is the script.

Create a Date field

kaushiknsolanki — Mon, 11 Jul 2011 09:30:57 GMT

Hi,

You have used MakeDate function to create a date, but you have used only 2 parameter try with 3 Parameter.

The one which you used is.

MakeDate(NewYear,Daily.FinMonth) as DailyDashboardDate

Make it as

MakeDate(NewYear,Daily.FinMonth,1) as DailyDashboardDate;

So here the first Parameter is Year, Second is Month and Third is Day.

Regards,

Kaushik Solanki

Re: Create a Date field

Miguel_Angel_Baeyens — Mon, 11 Jul 2011 09:54:20 GMT

Hi,

For what it's worth here's no need for that, since MakeDate() will work fine with even one parameter (year) and will return the first possible value for that year.

MakeDate(2011, 7)

will return the first day for year 2011 month 7. If you do have a field where you store the complete timestamp of the date, then yes, you could use that as the third parameter. All three parameters must be numeric.

But in any case, I'd create a unique date field in each table (using MakeDate will do) and link that to a master calendar table, where you have all the date dimensions (year, month, quarter, week, day, whatever else).

Here is what I'd do.

Dump0:
LOAD 
     Date(MakeDate(Year, Month)) AS Date,
...
FROM  (qvd);
CONCATENATE LOAD 
     Date(MakeDate(Year, Month)) AS Date,
...
FROM  (qvd);
CONCATENATE LOAD
     Date(MakeDate(Year, Month)) AS Date,
...
FROM  (qvd)
WHERE Match(Class, 900, 700) > 0 AND Match(Code, 2000, 3800, 3810, 3900, 2100, 220, 2400, 2320, 2310, 2500, 2700, 2800, 2900, 3451, 3400, 3530, 3300, 2330) > 0;
// Using match() instead of the conditional, for clarity and perhaps performance
CalendarMinMax:
LOAD Max(Date) AS MaxDate,
     Min(Date) AS MinDate
RESIDENT Dump0:
// Getting highets and lowest possible values for actual dates
LET vMaxDate = FieldValue('MaxDate', 0);
LET vMinDate = FieldValue('MinDate', 0);
DROP TABLE CalendarMinMax;
Calendar:
LOAD Date($(vMinDate) + RowNo() -1) AS Date,
     Year($(vMinDate) + RowNo() -1) AS Year,
     Month($(vMinDate) + RowNo() -1) AS Month,
     Week($(vMinDate) + RowNo() -1) AS Week,
     ... // all the rest of required date dimensions
AUTOGENERATE $(vMaxDate) - $(vMinDate) +1;

I haven't tested the code and I may have made some syntax mispellings. Anyway, the idea is to have one table you can easily modify to add or remove date fields and a separte fact table.

Hope that makes sense and helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Create a Date field

Mon, 11 Jul 2011 10:23:46 GMT

I am not sure if u hv understood my problm completely..

Suppose i have uploaded data today...

then month nd Year are being fetched frm data itself....but I want to add day also.

since I have uploaded data today then the data should be stored at today's date (Month nd year fetched frm above nd Day will be relaod date.).Now again when i am loading data tomorrow then data should be saved at tomorrow's date.

I need this because i want to see increment from Yesterday's data.

what i am planning is to do:

MonthName(MakeDate(NewYear,Daily.FinMonth,day(reloadtime())),3) as DailyDashboardMonthYear,

MakeDate(NewYear,Daily.FinMonth,day(reloadtime())) as DailyDashboardDate;

but this will nt wrk as previous 'Day' will get replaced by today's 'Day'

and not concatenated.

M confused how sholud i do linking and all...

Re: Create a Date field

Miguel_Angel_Baeyens — Mon, 11 Jul 2011 10:50:32 GMT

Hi Erika,

I see two different things here: in one hand the "incremental" load based on some timestamp that is not in your database and depends on when the files are loaded and on the other hand the date dimensions used later in your charts and expressions.

In regards to the code

MonthName(MakeDate(NewYear, Daily.FinMonth, day(reloadtime())), 3) AS DailyDashboardMonthYear

That will create a field with the values in NewYear, Daily.FinMonth and today's day number. If all that is meant to be "today", then why not using instead a cleaner and simpler

Date(Today()) AS Date

You can link that to the Calendar table where you have the month, monthname, and any other date related dimensions.

In regards to the load, do you have in your data source some way to identify the complete date of the records so you can use that field in a WHERE clause?

You can always do a two step load and store based on the code above, so you always load the QVD file, plus the records where date doesn't exist (something like this), or use a key field to load all records from your source except for those which key has been already loaded... Hence you always have a valid and complete date field you can use in your expressions.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica