topic Re: Master Calender Script Error.... in QlikView

Master Calender Script Error....

MK_QSL — Tue, 09 Jul 2013 09:20:41 GMT

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.

Re: Master Calender Script Error....

Tue, 09 Jul 2013 10:01:34 GMT

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.

Re: Master Calender Script Error....

Tue, 09 Jul 2013 10:18:51 GMT

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?

Re: Master Calender Script Error....

MK_QSL — Tue, 09 Jul 2013 10:56:48 GMT

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

Re: Master Calender Script Error....

Tue, 09 Jul 2013 11:16:43 GMT

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

Re: Master Calender Script Error....

MK_QSL — Tue, 09 Jul 2013 11:41:10 GMT

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?

Re: Master Calender Script Error....

Tue, 09 Jul 2013 11:49:22 GMT

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.

Re: Master Calender Script Error....

MK_QSL — Tue, 09 Jul 2013 11:56:36 GMT

Thanks for your help...!!