Solved: Working with dates in Qlikview - Qlik Community

Anonymous · ‎2012-08-23

Hi there,

I would like to know how most of you use dates in Qlikview. I've been using Qlikview for about a month now but aren't sure what is the best way when importing multiple tables each containing a date column. I import all information into Qlikview from Excel files and most of them have a date column.

From what I've read it looks like one must rename each date column label so that the dates would only be applicable to that table and so that tables aren't joined with the date columns.

Is it a good idea to join tables using the "Date" column and one other column for example "Customer" or is it better to rename each date column label?
Is it better to import the date in 3 seperate columns for example "Day", "Month" and "Year" or can you create 3 different list boxes in Qlikview from one date column?

A reply would be much appreciated.

Report Inappropriate Content · ‎2012-08-23

1. You only want to join on dates if applicable to your data. Say you have a date with customer that is the create date. if you join this with orders on the order date you will loose all orders not on that date. In most cases it is better to just rename to the table applied to.

2. I have found it is usually better to change this in the load script since if you do it in a list box you are still only selecting from the date field. Say you do it in the list box for year. if you select say 2011 it will say in current selections

Date: 365 of (total or in my case 1096)

if this is done in the script then you just use the new year field and selection will show:

Year: 2011

View solution in original post

Report Inappropriate Content · ‎2012-08-23

1. You only want to join on dates if applicable to your data. Say you have a date with customer that is the create date. if you join this with orders on the order date you will loose all orders not on that date. In most cases it is better to just rename to the table applied to.

2. I have found it is usually better to change this in the load script since if you do it in a list box you are still only selecting from the date field. Say you do it in the list box for year. if you select say 2011 it will say in current selections

Date: 365 of (total or in my case 1096)

if this is done in the script then you just use the new year field and selection will show:

Year: 2011

Report Inappropriate Content · ‎2012-08-23

I agree with Marc; we use the Year(date), Month(date) feature in the Load statement to create these fields. For example, we have an SaleStart date and a SaleClose date. By using "Month(SaleStart) as SStMnth" and "Month(SaleClose) As SClMonth", we can then easily select all the sales that started in January and were closed in June. Makes things a lot more flexible at the risk of cluttering the screen with a litany of listboxes.

Anonymous · ‎2012-08-25

Thanks for the reply. I didn't know that you could use the functions Year(Date) in the load script but I tried it and it works! Thanks for the help.