topic Re: MasterCalender question - duplicates in QlikView

MasterCalender question - duplicates

Fri, 04 Jan 2013 16:32:07 GMT

Folks,

I am loading a Master Calender with this scirpt (copied from this forum and adjust slightly):


Let varMinDate = 41276;
Let varMaxDate = 41278;
// DROP Table Temp;
TempCalendar:
LOAD
               $(varMinDate) + Iterno()-1 As Num,
               Date(($(varMinDate) + IterNo() - 1), 'DD.MM.YYYY') as TempDate
               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
               TempDate AS MasterDaily,
               hour(TempDate) As Hour,
               Date(TempDate, 'DD.MM.YYYY') As FullDate,
               week(TempDate) As Week,
               Year(TempDate) As Year,
               Month(TempDate) As Month,
               Day(TempDate) As Day,
               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
               WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
// Drop Table TempCalendar;

Now the question:

As a result a get this table (using table box). Why do I get "duplicates" in the TempDate with the MasterDaily data.

Shouldn't it be a signle line for each each date? I am loading the TempDate, and the TempDate itself looks fine, but by buildung the table i see duplicates.

TempDate	MasterDaily	WeekDay
02.01.2013	02.01.2013	Mi
02.01.2013	03.01.2013	Do
02.01.2013	04.01.2013	Fr
03.01.2013	02.01.2013	Mi
03.01.2013	03.01.2013	Do
03.01.2013	04.01.2013	Fr
04.01.2013	02.01.2013	Mi
04.01.2013	03.01.2013	Do
04.01.2013	04.01.2013	Fr

Can somebody please comment and push me into the right direction,

Thanks,

Mathias

Re: MasterCalender question - duplicates

swuehl — Fri, 04 Jan 2013 16:37:12 GMT

I don't think you are getting duplicates here:

Your two tables are not linked by a common field, so using fields from the two tables in one table box will lead to an implicite cross product of all records of the two tables. That's what you see here.

Regards,

Stefan

Re: MasterCalender question - duplicates

Fri, 04 Jan 2013 16:41:16 GMT

Thanks, that was the right hint.

Re: MasterCalender question - duplicates

Fri, 04 Jan 2013 16:57:13 GMT

Thanks again swuehl,

here is the real issue where I am coming from. I am using this script:

Temp:
Load
               min(Date_Daily) as minDate,
               max(Date_Daily) as maxDate
Resident DAILY_DATA;
Let varMinDate = Num(Floor(Peek('minDate', 0, 'Temp')));
Let varMaxDate = Num(Floor(Peek('maxDate', 0, 'Temp')));
Trace MIN varMinDate;
Trace MAX varMaxDate;
DROP Table Temp;
TempCalendar:
LOAD
               $(varMinDate) + ((IterNo() - 1)/1440) As Num,
               Date(($(varMinDate) + ((IterNo() - 1)/1440)), 'DD.MM.YYYY hh:mm') as TempDate
               AutoGenerate 1 While ($(varMinDate) + ((IterNo() - 1)/1440)) < $(varMaxDate)+1;
MasterCalendar:
Load
               TempDate AS MasterDaily,
               Hour(TempDate) As MasterHour,
               Date(Num, 'DD.MM.YYYY hh:mm') As MasterFullDateNum,
               Date(TempDate, 'DD.MM.YYYY hh:mm') As MasterFullDate,
               week(TempDate) As MasterWeek,
               Year(TempDate) As MasterYear,
               Month(TempDate) As MasterMonth,
               Day(TempDate) As MasterDay,
               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
               WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

The "real data" is coming from millions of lines out of a CSV file. The CSV contains a field named: MasterDaily.

The data find together pretty much, but I get outputs like:

What I do not understand is, why I am getting this duplicate lines e. g. for 00:21 or 00:24.

I checked the raw data, and i got data in my CSV for 00:21 and 00:24.

But using this data in diagrams shows the ' - ' NULL value for some dimensions.

Do you have any idea on this as well? I thought I drill it down by myself, but I was on the wrong way with my tries around TempDate.

Thanks,

Mathias

Re: MasterCalender question - duplicates

swuehl — Mon, 07 Jan 2013 17:06:08 GMT

The data find together pretty much, but I get outputs like:
What I do not understand is, why I am getting this duplicate lines e. g. for 00:21 or 00:24.
I checked the raw data, and i got data in my CSV for 00:21 and 00:24.

I can't see your ouput you mention above, could you repost it?

I assume the values you created in your tmp calendar just don't match the values that you read from your csv (I mean, not for every record). There could be potential differences between the internal alogrithm QV uses to interpret your input records as timestamps and your calculation.

Could you maybe post a snippet of your csv and your qvw that reads that data in here to the forum?

Re: MasterCalender question - duplicates

Mon, 07 Jan 2013 22:24:19 GMT

Hi swuehl,

thanks for your feedback. I do not have the table anymore, as I made to many changes on the script meanwhile. But it hasn't matched all lines as you said. I did not managed to do it right for minutes records, and not just for days. I am using for now just days as this is sufficient for the moment.

Thanks again for your great help,

Regards,

Mathias

Re: MasterCalender question - duplicates

swuehl — Tue, 08 Jan 2013 16:31:14 GMT

Mathias,

I think this could be helpful for your problem also:

http://qlikviewnotes.blogspot.de/2011/10/correct-time-arithmetic.html

Drop a note if you need more assistance on this.

Stefan

Re: MasterCalender question - duplicates

Tue, 08 Jan 2013 16:51:05 GMT

@swuehl: This post is so so so usefull, thanks so much for sharing this.