Master Calender

[MK_QSL]

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

NO

01/07/2013

NO

....

....

....

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

How to avoid this?

[alexandros17]

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

[MK_QSL]

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.

[#4]

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

[MK_QSL]

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

[#6 · Accepted solution]

See attached file.

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

[alexandros17]

//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);