Error in expression: Date takes 1-2 parameters

[Anonymous]

Hi!

I have this script that doesn't work. Anyone who can help?

Let vMaxÅr = Year($(EndDate));

Date:

Load

%Date,

Year(%Date) & '_' & num(Month(%Date),'00') As %Year_Month,

Year(%Date) As Year,

QuarterName(%Date) As Quarter,

Month(%Date) As Month,

Date(%Date) As Date;

Load

Date($(StartDate)+(Iterno()-1),'YYYYMMDD') As %Date

Autogenerate 1

While

Date($(StartDate)+(Iterno()-1)) <= Date($(vEndDate));

The error message received is the follwoing;

"Error in expression:

Date takes 1-2 parameters

Load

Date(+(Iterno()-1),'YYYYMMDD') As %Date

Autogenerate 1

While

Date(+(Iterno()-1)) <= Date()"

Suggestions??

/J

[sunny_talwar]

Where are vEndDate and StartDate created?

[Anonymous]

Hi!

Sorry missed part of the script.

//================================================================================================

// Generates all dates from min to max date

//================================================================================================

// Mindate

tmpTable:

Load min(%Date) As minDate Resident Fact;

Let StartDate = Peek('minDate');

Drop Table tmpTable;

// Maxdate.

tmpTable:

Load max(%Date) As maxDate Resident Fact;

Let EndDate =  Peek('maxDate');

Drop Table tmpTable;

// Spara undan max År.

Let vMaxÅr = Year($(EndDate));

Date:

Load

%Date,

Year(%Date) & '_' & num(Month(%Date),'00') As %Year_Month,

Year(%Date) As Year,

QuarterName(%Date) As Quarter,

Month(%Date) As Month,

Date(%Date) As Date;

Load

Date($(StartDate)+(Iterno()-1),'YYYYMMDD') As %Date

Autogenerate 1

While

Date($(StartDate)+(Iterno()-1)) <= Date($(vEndDate));

// Skapa tabell med Månad och Månadsräknare

tmpTable:

Load distinct %Year_Month Resident Date;

Month:

Load

%Year_Month,

RowNo() As %MonthCounter,

%Year_Month As Year_Month

Resident

tmpTable

Order by

%Year_Month;

Drop table tmpTable;

Load * Inline [

ID_Overview, %Overview

1, 'SEK'

2, 'Local currency'

];

[sunny_talwar]

Can you just run this

// Mindate

tmpTable:

Load min(%Date) As minDate Resident Fact;

Let StartDate = Peek('minDate');

TRACE $(StartDate);

Drop Table tmpTable;

// Maxdate.

tmpTable:

Load max(%Date) As maxDate Resident Fact;

Let EndDate =  Peek('maxDate');

TRACE $(EndDate);

Drop Table tmpTable;

EXIT SCRIPT;

Also, you are using vEndDate in your Date table rather than using EndDate

[Anonymous]

Hi!

Tried to rerun this without success. Still got the same error message as I started with.

tmpTable:

Load min(%Date) As minDate Resident Fact;

Let StartDate = Peek('minDate');

TRACE $(StartDate);

Drop Table tmpTable;

// Maxdate.

tmpTable:

Load max(%Date) As maxDate Resident Fact;

Let EndDate =  Peek('maxDate');

TRACE $(EndDate);

Drop Table tmpTable;

// Spara undan max År.

Let vMaxÅr = Year($(EndDate));

Date:

Load

%Date,

Year(%Date) & '_' & num(Month(%Date),'00') As %Year_Month,

Year(%Date) As Year,

QuarterName(%Date) As Quarter,

Month(%Date) As Month,

Date(%Date) As Date;

Load

Date($(StartDate)+(Iterno()-1),'YYYYMMDD') As %Date

Autogenerate 1

While

Date($(StartDate)+(Iterno()-1)) <= Date($(EndDate));

// Skapa tabell med Månad och Månadsräknare

tmpTable:

Load distinct %Year_Month Resident Date;

Month:

Load

%Year_Month,

RowNo() As %MonthCounter,

%Year_Month As Year_Month

Resident

tmpTable

Order by

%Year_Month;

Drop table tmpTable;

[pablolabbe]

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others

[hic · Accepted solution]

In principle, it should work. But there are several things that could go wrong, depending on your data...

I suggest you

1) Use the Floor()-function to define your date variables (to make sure that you get an integer)

2) Use a table reference in the Peek()-function

3) Use distinct in your calendar

// Mindate

tmpTable:

Load min(%Date) As minDate Resident Fact;

Let StartDate = Floor(Peek('minDate',0,'tmpTable'));

Drop Table tmpTable;

// Maxdate.

tmpTable:

Load max(%Date) As maxDate Resident Fact;

Let EndDate =  Floor(Peek('maxDate',0,'tmpTable'));

Drop Table tmpTable;

// Spara undan max År.

Let vMaxÅr = Year($(EndDate));

Date:

Load distinct

%Date,

Year(%Date) & '_' & num(Month(%Date),'00') As %Year_Month,

Year(%Date) As Year,

QuarterName(%Date) As Quarter,

Month(%Date) As Month,

Date(%Date) As Date;

Load

Date($(StartDate)+(Iterno()-1),'YYYYMMDD') As %Date

Autogenerate 1

While

Date($(StartDate)+(Iterno()-1)) <= Date($(EndDate));

[Anonymous]

Thank you!!

The app is working properly now.

/J