Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Create Records future days

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.

1 Solution

Accepted Solutions
preminqlik
Specialist II
Specialist II

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;

View solution in original post

7 Replies
Anonymous
Not applicable
Author

Set your master calendar's max date as your required date.

It should work

Not applicable
Author

While loading the script how can i achieve it?

Anonymous
Not applicable
Author

Load

Min(Date) as MinDate,
'42369'
as MaxDate

add the above script in your master calendar.

Note 42369 is 31st Dec '15

ashfaq_haseeb
Champion III
Champion III

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

Not applicable
Author

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?

Not applicable
Author

i think for year

=Year(Today())+1

preminqlik
Specialist II
Specialist II

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;