Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

MasterCalender question - duplicates

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

1 Solution

Accepted Solutions
MVP
MVP

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

7 Replies
MVP
MVP

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

Not applicable

Re: MasterCalender question - duplicates

Thanks, that was the right hint.

Not applicable

Re: MasterCalender question - duplicates

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

MVP
MVP

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?

Not applicable

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

MVP
MVP

Re: MasterCalender question - duplicates

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

Not applicable

Re: MasterCalender question - duplicates

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

Community Browser