Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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));
Where are vEndDate and StartDate created?
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'
];
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
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;
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
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));
Thank you!!
The app is working properly now.
/J