topic Re: Create Records future days in QlikView

Create Records future days

Mon, 19 May 2014 08:57:27 GMT

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.

Re: Create Records future days

Anonymous — Mon, 19 May 2014 09:12:28 GMT

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

It should work

Re: Create Records future days

Mon, 19 May 2014 09:18:42 GMT

While loading the script how can i achieve it?

Re: Create Records future days

Anonymous — Mon, 19 May 2014 09:24:17 GMT

Load

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

add the above script in your master calendar.

Note 42369 is 31st Dec '15

Re: Re: Create Records future days

ashfaq_haseeb — Mon, 19 May 2014 09:46:48 GMT

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

Re: Create Records future days

Mon, 19 May 2014 10:26:44 GMT

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?

Re: Create Records future days

Mon, 19 May 2014 10:38:26 GMT

i think for year

=Year(Today())+1

Re: Create Records future days

preminqlik — Mon, 19 May 2014 11:16:34 GMT

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;