7 Replies Latest reply: Jan 8, 2013 11:51 AM by Mathias Mühlfelder

# MasterCalender question - duplicates

Folks,

```Let varMinDate = 41276;
Let varMaxDate = 41278;
// DROP Table Temp;

TempCalendar:
\$(varMinDate) + Iterno()-1 As Num,
Date((\$(varMinDate) + IterNo() - 1), 'DD.MM.YYYY') as TempDate
AutoGenerate 1 While \$(varMinDate) + IterNo() -1 <= \$(varMaxDate);

MasterCalendar:
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

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

Thanks, that was the right hint.

• ###### Re: MasterCalender question - duplicates

Thanks again swuehl,

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

```Temp:
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:
\$(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:
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

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

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