MasterCalender question - duplicates

[#1]

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

[swuehl · Accepted solution]

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

[#3]

Thanks, that was the right hint.

[#4]

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

[swuehl]

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?

[#6]

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

[swuehl]

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

[#8]

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