Master Calender Script Error....

[MK_QSL]

Today morning, while Reloading the script in my QlikView file, I got an error in Master Calender as below...

Script Line Error:

InvoiceDateField:

Load

     Date(41122 + rowno() - 1) as InvoiceTempDate

Autogenerate

-41122 + 1

I can see that variable containing Today giving me wrong date....

varInvoiceToday = 05/07/2013 should be 09/07/13...

This could be the reason behing script load fail.

Can someone please tell me what could be wrong?

Thanks.

[#2]

Usually you create a calendar between a minimum and maximum date. I think you tried to do that, but the variable containing the minimum date is empty.

[#3]

Patrick is right, your "minDate" variable (I don't know how you named it) is empty or null.

Could you post the LET statement where you assign a value to this variable?

[MK_QSL]

I have enclosed EXCEL and QVW file both....

[#5 · Accepted solution]

Your problem was that the vMaxDate was not filled.

I changed it to:

SALE_temp:

LOAD

    [Invoice No],

     [Invoice Date],

     QTY,

     [Price (INV)],

     [Inv Total],

     [EX RATE],

     [Cost Price],

     [Total Cost Price]

FROM

SalesInvoices.xlsx

(ooxml, embedded labels, table is [Sales 2013]);

SALE:

Load

    *,

     [Invoice Date] as InvoiceDate

resident SALE_temp where not isnull([Invoice Date]) order by [Invoice Date] asc;

drop table SALE_temp;

// ****************Creating the Invoice Calendar *************

LET vInvoiceMinDate = num(Peek('InvoiceDate', 0,'SALE'));

LET vInvoiceMaxDate = Num(Peek('InvoiceDate', -1,'SALE'));

LET vInvoiceToday =num(today());

[MK_QSL]

Dear Patrick,

This script is working fine but i really want to understand below...

SALE:

Load

    *,

     [Invoice Date] as InvoiceDate

resident SALE_temp where not isnull([Invoice Date]) order by [Invoice Date] asc;

Why this not isnull is needed for my excel file?

Is there any specific field in InvoiceDate which required above script?

[#7]

The Invoice Date was causing troubles because there was a space in it. Initialy I used the square brackets but that caused the variable to remain empty. Therefore I renamed the field to one that doesn't have a space. Afterwards you could also drop the field.

Without ordering the file the max date was not filled. After ordering the mindate remained null. So there is a Invoice Date empty or one is not interpreted as a Date. Maybe an empty record has been read. I did not have time to do a good research.

Tables read from an excel file cannot be sorted. Therefore I first read it as is and afterwards I order it.

[MK_QSL]

Thanks for your help...!!