topic Re: Duplicate dates within my Master Calendar in QlikView

Duplicate dates within my Master Calendar

Tue, 28 Jan 2014 20:47:19 GMT

When I create my Master Calendar I am getting multiple dates (basically i'm getting dates for each and every row of my data).

Example of MasterCalendar output:

11/13/2013

11/14/2013

11/15/2013

The code originally LOADS the data in like this:

LOAD Distinct

DATE(STARTTIME) AS [CALL DATE],

MONTH(STARTTIME) AS [CALL MONTH],

fieldA,

fieldB,

and so on.

When i create the Min/Max dates, everything appears fine.

When i create the TempCal like so:

TempCal:

LOAD

Date($(vMinDate)+rowno()-1)AS TempDate

AutoGenerate

$(vMaxDate)-$(vMinDate)+1;

Drop Table MinMax;

This also shows only one date:

11/13/2013

11/14/2013

11/15/2013

However, when i create the Master Calendar (as shown below) i get multiples.

MasterCalendar:

LOAD Distinct

TempDate AS [Call Date]

Resident

TempCal

Order By TempDate ASC;

Drop Table TempCal;

How do I prevent duplicates date in my Master Calendar, yet create a connection back to my original table?

(I have read other posts that state to only use a "Date" Field and not a "Date/Time" field, however this table only has the Date/Time Field to use.

In addition, pulling out the Date from the Load script from above and doing a distinct in the creation of the MasterCalendar should take care of that issue but it is not.

Thanks

Re: Duplicate dates within my Master Calendar

Nicole-Smith — Tue, 28 Jan 2014 20:49:34 GMT

You should only use a Date Field and not a Date/Time field. To get rid of the time, wrap the field in floor():

DATE(floor(STARTTIME))

Re: Duplicate dates within my Master Calendar

Tue, 28 Jan 2014 20:56:55 GMT

Thank you Nicole, That did the trick.

Re: Duplicate dates within my Master Calendar

Anonymous — Tue, 28 Jan 2014 20:59:02 GMT

If you have STARTTIME values like this

01/28/2013 10:00

01/28/2013 10:30

01/28/2013 11:00

They are three different values, even if you show them as date. The floor(STARTDATE) removes time portion, and you get one value 01/28/2013.

Re: Duplicate dates within my Master Calendar

Nicole-Smith — Tue, 28 Jan 2014 21:01:28 GMT

Please mark correct and helpful answers so others can find solutions to their problems too