topic Re: Master Calender in QlikView

Master Calender

MK_QSL — Fri, 05 Jul 2013 12:15:51 GMT

I have filed name Collection which is showing dates when customer has collected consignment.

Wherever there is no collection done, it is being mentioned by NO.

So it looks like below

03/05/2013

04/06/2013

01/07/2013

....

Now, when I am trying to create a master calendar for collection, it is giving me error.

How to avoid this?

Re: Master Calender

alexandros17 — Fri, 05 Jul 2013 12:18:33 GMT

Replace NO with 2099/12/12 so this is a date too and you cam manage it esily

Re: Master Calender

MK_QSL — Fri, 05 Jul 2013 12:23:12 GMT

isnt there any other way.

This NO value is also being used under many objects and dont want to replace the same.

Also replacing NO with odd date will increase my calender data also.

Re: Master Calender

Fri, 05 Jul 2013 12:24:18 GMT

Post, pleace, your example qvw or script and what error do you getting.

Re: Master Calender

MK_QSL — Fri, 05 Jul 2013 12:32:05 GMT

Excel and QVW file enclosed.

Also, Invoice Date and Collection date both are different.

What is the best way to manage them by Master Calender?

Should I make two different Master Calender? i.e. one for Invoice Date and 2nd for Collection Date

Re: Master Calender

Fri, 05 Jul 2013 12:46:35 GMT

See attached file.

Yes you need 2 diffrent master calenders for Invoice Date and Collection.

Re: Master Calender

alexandros17 — Fri, 05 Jul 2013 12:52:00 GMT

//CHANGE YOUR SCRIPT IN THIS WAY

SSS:

LOAD [Customer Name],

     Country,

     [Invoice No],

     [Invoice Date],

     CURRENCY,

     QTY,

     PRICE,

     [EX RATE],

     Collection

FROM

//

Sale.xlsx

(ooxml, embedded labels, table is Sheet1);

// ****************Creating the Collection Calendar *************

Mi:

LOAD min(Collection) as mini Resident SSS where Collection <> 'NO';

Ma:

LOAD max(Collection) as maxi Resident SSS where Collection <> 'NO';

LET vCollectionMinDate = Peek('mini',0,'Mi');

LET vCollectionMaxDate = Peek('maxi',0,'Ma');

DROP Tables Mi,Ma;

//LET vCollectionMinDate      = Num(Peek('Collection', 0, 'SALE'));

//LET vCollectionMaxDate      = Num(Peek('Collection', -1, 'SALE'));

LET vCollectionToday      = Num(today());

CollectionDateField:

LOAD

if($(vCollectionMinDate)<>'NO',

date ($(vCollectionMinDate) + rowno() -1),

null()) as CollectionTempDate

AUTOGENERATE

if($(vCollectionMaxDate)<>'NO' and $(vCollectionMinDate)<>'NO',

$(vCollectionMaxDate)- $(vCollectionMinDate)+1);