Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
I have this scenario,
I have StoreOrders Table and I have brand table(10 brands). I want to create records in the StoreOrders table for all brands for all months for future month and next year till 2015 with qty, price set to 0.
How can I achieve this?
Thank you.
after creating master calendar upto next year foloow below script
DUMMY_TABLE:
Load distinct storeOrder,
brand
resident Original;
join
Load Date as Trasaction_Date
resident Master calendar;
generally the above procedure we call it as CARTESIAN PRODUCT .now concatenate the above values to orginal with giving 0 as quantity see below
FINAL_TRASACTION:
Load storeOrder,
brand,
Qty
Trasaction_Date
resident Orginal;
concatenate
Load storeOrder,
brand,
Trasaction_Date,
0 as Qty
resident DUMMY_TABLE;
drop table DUMMY_TABLE;
drop table Orginal;
Set your master calendar's max date as your required date.
It should work
While loading the script how can i achieve it?
Load
Min(Date) as MinDate,
'42369' as MaxDate
add the above script in your master calendar.
Note 42369 is 31st Dec '15
Hi,
Check the below script.
Let varMinDate = num(MakeDate(2000,1,1));
Let varMaxDate = num(MakeDate(2020,12,31));
//*************** Temporary Calendar ***************
TempCalendar:
LOAD
$(varMinDate) + rowno() - 1 AS Num,
date($(varMinDate) + rowno() - 1) AS TempDate
AUTOGENERATE
$(varMaxDate) - $(varMinDate) + 1;
//*************** Master Calendar ***************
MasterCalendar:
LOAD TempDate AS Date,
Week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
Year(TempDate)&Week(TempDate) as YEARWEEK_NUM,
'Q'&ceil(month(TempDate)/3) AS Quarter
RESIDENT TempCalendar
ORDER BY TempDate ASC;
DROP TABLE TempCalendar;
Store MasterCalendar into MasterCalendar.qvd(qvd);
RESIDENT TempCalendar
ORDER BY TempDate ASC;
DROP TABLE TempCalendar;
Store MasterCalendar into MasterCalendar.qvd(qvd);
Regards
ASHFAQ
Thank you all it works... Now i have future dates in the Master Table..
But I want to associated every brand with it. How do I do that?
i think for year
=Year(Today())+1
after creating master calendar upto next year foloow below script
DUMMY_TABLE:
Load distinct storeOrder,
brand
resident Original;
join
Load Date as Trasaction_Date
resident Master calendar;
generally the above procedure we call it as CARTESIAN PRODUCT .now concatenate the above values to orginal with giving 0 as quantity see below
FINAL_TRASACTION:
Load storeOrder,
brand,
Qty
Trasaction_Date
resident Orginal;
concatenate
Load storeOrder,
brand,
Trasaction_Date,
0 as Qty
resident DUMMY_TABLE;
drop table DUMMY_TABLE;
drop table Orginal;