topic Re: Autogenerate Dates fir inline load in QlikView

Autogenerate Dates fir inline load

dimak123 — Tue, 19 May 2015 12:14:09 GMT

Hi to all

I have a table:

Machine	Shift	Production
1	Morning	250
1	Noon	250
1	Evening	250
2	Morning	242
2	Noon	242
2	Evening	242
3	Morning	150
3	Noon	150
3	Evening	150
4	Morning	120
4	Noon	120
4	Evening	120
5	Morning	65
5	Noon	65
5	Evening	65
6	Morning	225
6	Noon	225
6	Evening	225
7	Morning	180
7	Noon	180
7	Evening	180
8	Morning	914
8	Noon	914

I would like to add a field of date from 01/01/2014 till yesterday every load

how do i write this?

thanks a head

Re: Autogenerate Dates fir inline load

sunny_talwar — Tue, 19 May 2015 12:17:20 GMT

You can create a Master Calendar for dates, but how do you plan to connect your above table to the master calendar? There are no dates in the above table.

Script for Master Calendar:

LET vMinDate = Num('01/01/2014');

LET vMaxDate = Num(Today() - 1);

TempCalendar:

LOAD

$(vMinDate) + IterNo() - 1 as Num,

Date($(vMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);

[Master Calendar]:

LOAD

TempDate as Date,

Week(TempDate) as Week,

Year(TempDate) as Year,

Month(TempDate) as Month,

Day(TempDate) as Day,

Weekday(TempDate) as WeekDay,

WeekStart(TempDate) as WeekStart,

'Q' & ceil(month(TempDate) / 3) as Quarter,

'Q' & Ceil(Month(TempDate)/3) & '-' & Year(TempDate) as QuarterYear,

MonthName(TempDate) as MonthYear,

Week(TempDate)&'-'&Year(TempDate) as WeekYear

Resident TempCalendar

Order By TempDate ASC;

DROP Tables Temp, TempCalendar;

LET vMinDate = Null();

LET vMaxDate = Null();

HTH

Best,

Sunny

Re: Autogenerate Dates fir inline load

dimak123 — Tue, 19 May 2015 12:25:49 GMT

I dont need a master calendar

i just want to add date to my table, as the number of machines and shifts for every date

Re: Autogenerate Dates fir inline load

sunny_talwar — Tue, 19 May 2015 12:27:59 GMT

For each Machine, shift, production you want to add ~500 days????

Re: Autogenerate Dates fir inline load

dimak123 — Tue, 19 May 2015 12:30:33 GMT

yes

Re: Autogenerate Dates fir inline load

crusader_ — Tue, 19 May 2015 12:36:04 GMT

Hi,

Do you really want to make Cartesian product?

For every Machine and Shift add full range of dates...

If so just pick part of master calendar code and JOIN it to your table

LET vMinDate = Num('01/01/2014');

LET vMaxDate = Num(Today() - 1);

TempCalendar:

JOIN(YourTable)

LOAD

Date($(vMinDate) + IterNo() - 1) as Date

AutoGenerate 1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);

I can't imagine where it could be useful but you are free to choose...

Regards,

Andrei

Re: Autogenerate Dates fir inline load

dimak123 — Tue, 19 May 2015 12:42:20 GMT

thanks