Autogenerate Dates fir inline load

[dimak123]

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

[sunny_talwar]

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

[dimak123]

Hi

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

[sunny_talwar]

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

[dimak123]

yes

[crusader_ · Accepted solution]

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

[dimak123]

thanks