topic Script loading invalid expression in QlikView

Script loading invalid expression

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I'm practising script. This one works:

Directory;
Maintable:
LOAD date(date#(SalesDate,'MM/DD/YYYY'),'DD-MM-YYYY') as SalesDate, 
     SalesVolume
FROM
practicecalendar.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);


MasterCalendar:
Load
Date(SalesDate) as SalesDate,
Year(SalesDate) as Year,
'Q'&ceil(month(SalesDate)/3) as Quarter,
Day(SalesDate) as Day,
Week(SalesDate) as Week,
Month(SalesDate) as Month,
Resident Maintable;

But then I created two additional fields and get the error that the expression is invalid.

Min(SalesDate) as MinDate,

Max(SalesDate) as MaxDate

Directory;
Maintable:
LOAD date(date#(SalesDate,'MM/DD/YYYY'),'DD-MM-YYYY') as SalesDate, 
     SalesVolume
FROM
practicecalendar.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);


MasterCalendar:
Load
Date(SalesDate) as SalesDate,
Year(SalesDate) as Year,
'Q'&ceil(month(SalesDate)/3) as Quarter,
Day(SalesDate) as Day,
Week(SalesDate) as Week,
Month(SalesDate) as Month,
Min(SalesDate) as MinDate,
Max(SalesDate) as MaxDate
Resident Maintable;

Can anyone please help explain the reason? And how to detect the cause so that next time I can get it right?

Thank you.

Re: Script loading invalid expression

Anil_Babu_Samineni — Tue, 18 Sep 2018 04:02:29 GMT

Try this way?

Maintable:

LOAD date(date#(SalesDate,'MM/DD/YYYY'),'DD-MM-YYYY') as SalesDate,

SalesVolume

FROM

practicecalendar.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

MasterCalendar:

Load

Date(SalesDate) as SalesDate,

Year(SalesDate) as Year,

'Q'&ceil(month(SalesDate)/3) as Quarter,

Day(SalesDate) as Day,

Week(SalesDate) as Week,

Month(SalesDate) as Month

Resident Maintable;

Load Min(SalesDate) as MinDate,

Max(SalesDate) as MaxDate

Resident Maintable;

But, I really didn't understand why you need Min and Max Date if you are not using anywhere As of Now?

Re: Script loading invalid expression

Anonymous — Tue, 18 Sep 2018 04:23:54 GMT

Many thanks Anil. Being a novice, can I understand why do we need to separating it into 2 Loads with 2 resident Maintable? In my simple mind, Min/Max is just like other above functions.

Re: Script loading invalid expression

Anil_Babu_Samineni — Tue, 18 Sep 2018 09:07:52 GMT

I am creating Min and Max dates from Maintable using SalesDate. Then, We can create separate table for Calendar to get associated with All fact fields into Dimension tables and Fields from calendar.