Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
As the title says it, my QlikView is stuck on every new script run, changing calendar values to update my table rows.
A script run takes about 5mins, which is hilarious.
I import about 10mb information from Excel file.
Is it maybe my following condition that makes my application so slow?
if(CalendarDate<CalendarDate2,
if(MyDate > date(CalendarDate, 'DD/MM/YYYY') AND MyDate < date(CalendarDate2, 'DD/MM/YYYY'), date(MyDate,'DD/MM/YYYY'),'Not in Interval matching')
,
if(MyDate > date(CalendarDate2, 'DD/MM/YYYY') AND MyDate < date(CalendarDate, 'DD/MM/YYYY'), date(MyDate,'DD/MM/YYYY'),'Not in Interval matching'))
What it does, is that I have a starting Date ( CalendarDate ) and an ending Date (CalendarDate2).
If I remove the IF conditions and leave MyDate only, everything is fine and the application isn't stuck on that part, but if I run again a script, it is stuck again ...
Any idea where it may come from?
I'm logging with Citrix into a virtual machine and start from there my QLikView application and checked if I get enough ressources, 32GB RAM available.
Thanks.
Hello,
It was a mistake from my side, because I was trying to load CalendarDate, and compare the WHOLE dates with my table date. Imagine 2010-2013 = 1000 dates to compares. Now imagine my import from Excel has 1000rows too, means 1000*1000=1.000.000 different checks that must be done. That's why my memory was stuck and said: No memory allocated.
I hope the following code will help people to understand a bit more how it works correctly and that it is important to store selections in a variable and to compare with that variable.
But still during script execution it is slow. At beginning it was a issue from server that I didn't get enough memory allocated and that the memory wasn't free correctly after closing an application. ( some Citrix issues )
Thanks for your interest.
******************************************************************************************
IMPORT_ALL:
LOAD
NameApp,
[Current State],
Application,
[Date raised] as [MyDate]
FROM myexcel.xls;
******************************************************************************************
I did following:
Created 2 variables
myStartSelection=GetFieldSelections(CalendarDayStart) // which is my start calendar, the start date I selected
myEndSelectionGetFieldSelections(CalendarDayStart2) // which is my end calendar, the end date I selected
******************************************************************************************
On my Chart, I created "Add Calculated Dimensions", to check if even some dates are shown if the interval changes
=IF(myStartSelection<>'' OR myEndSelection<>'',
if(myStartSelection<myEndSelection,
if(MyDate>= myStartSelection AND MyDate <= myEndSelection, date(MyDate,'DD/MM/YYYY'),'Not in Interval matching')
,
if(MyDate >= myEndSelection AND MyDate <= myStartSelection, date(MyDate,'DD/MM/YYYY'),'Not in Interval matching'))
,
MyDate)
******************************************************************************************
And to check if my variables are correct selected I created a Text Object.
=if(date(myStartSelection)<=date(myEndSelection),
'Period: ' &Date(myStartSelection) & ' - ' & Date(myEndSelection)
,
'Period: ' &Date(myEndSelection) & ' - ' & Date(myStartSelection))
******************************************************************************************
My Calendar looks like:
LET vDateMin = Num(MakeDate(2010,1,1));
LET vDateMax = Floor(MonthEnd(Today()));
LET vDateToday = Num(Today());
TempCalendar:
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber,
Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);
Calendar:
LOAD
Date(TempDate) AS CalendarDate,
// Standard Date Objects
Day(TempDate) AS CalendarDayOfMonth,
WeekDay(TempDate) AS CalendarDayName,
Week(TempDate) AS CalendarWeekOfYear,
Month(TempDate) AS CalendarMonthName,
'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,
Year(TempDate) AS CalendarYear,
// Calendar Date Names
WeekName(TempDate) as CalendarWeekNumberAndYear,
MonthName(TempDate) as CalendarMonthAndYear,
QuarterName(TempDate) as CalendarQuarterMonthsAndYear,
// Start Dates
DayStart(TempDate) as CalendarDayStart,
WeekStart(TempDate) as CalendarWeekStart,
MonthStart(TempDate) as CalendarMonthStart,
QuarterStart(TempDate) as CalendarQuarterStart,
YearStart(TempDate) as CalendarYearStart,
// End Dates
DayEnd(TempDate) as CalendarDayEnd,
WeekEnd(TempDate) as CalendarWeekEnd,
MonthEnd(TempDate) as CalendarMonthEnd,
QuarterEnd(TempDate) as CalendarQuarterEnd,
YearEnd(TempDate) as CalendarYearEnd,
// Combo Dates
'Q' & Ceil(Month(TempDate)/3) & '/' & Year(TempDate) AS CalendarQuarterAndYear,
Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS CalendarYearAndQuarter,
'Wed ' & DayStart(WeekStart(TempDate) + 3) as CalendarWednesdays
RESIDENT TempCalendar ORDER BY TempDate ASC;
DROP TABLE TempCalendar;
LET vDateMin = Num(MakeDate(2010,1,1));
LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));
LET vDateToday = Num(Today());
LET vDateMin2 = Num(MakeDate(2010,1,1));
LET vDateMax2 = Floor(MonthEnd(Today()));
LET vDateToday2 = Num(Today());
This problem occurs during script execution only? And MyDate is a field in every row? Care to include the complete script, or at least the section of the script that contains the MyDate and CalendarDatex Load code?
Peter
Hello,
It was a mistake from my side, because I was trying to load CalendarDate, and compare the WHOLE dates with my table date. Imagine 2010-2013 = 1000 dates to compares. Now imagine my import from Excel has 1000rows too, means 1000*1000=1.000.000 different checks that must be done. That's why my memory was stuck and said: No memory allocated.
I hope the following code will help people to understand a bit more how it works correctly and that it is important to store selections in a variable and to compare with that variable.
But still during script execution it is slow. At beginning it was a issue from server that I didn't get enough memory allocated and that the memory wasn't free correctly after closing an application. ( some Citrix issues )
Thanks for your interest.
******************************************************************************************
IMPORT_ALL:
LOAD
NameApp,
[Current State],
Application,
[Date raised] as [MyDate]
FROM myexcel.xls;
******************************************************************************************
I did following:
Created 2 variables
myStartSelection=GetFieldSelections(CalendarDayStart) // which is my start calendar, the start date I selected
myEndSelectionGetFieldSelections(CalendarDayStart2) // which is my end calendar, the end date I selected
******************************************************************************************
On my Chart, I created "Add Calculated Dimensions", to check if even some dates are shown if the interval changes
=IF(myStartSelection<>'' OR myEndSelection<>'',
if(myStartSelection<myEndSelection,
if(MyDate>= myStartSelection AND MyDate <= myEndSelection, date(MyDate,'DD/MM/YYYY'),'Not in Interval matching')
,
if(MyDate >= myEndSelection AND MyDate <= myStartSelection, date(MyDate,'DD/MM/YYYY'),'Not in Interval matching'))
,
MyDate)
******************************************************************************************
And to check if my variables are correct selected I created a Text Object.
=if(date(myStartSelection)<=date(myEndSelection),
'Period: ' &Date(myStartSelection) & ' - ' & Date(myEndSelection)
,
'Period: ' &Date(myEndSelection) & ' - ' & Date(myStartSelection))
******************************************************************************************
My Calendar looks like:
LET vDateMin = Num(MakeDate(2010,1,1));
LET vDateMax = Floor(MonthEnd(Today()));
LET vDateToday = Num(Today());
TempCalendar:
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber,
Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);
Calendar:
LOAD
Date(TempDate) AS CalendarDate,
// Standard Date Objects
Day(TempDate) AS CalendarDayOfMonth,
WeekDay(TempDate) AS CalendarDayName,
Week(TempDate) AS CalendarWeekOfYear,
Month(TempDate) AS CalendarMonthName,
'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,
Year(TempDate) AS CalendarYear,
// Calendar Date Names
WeekName(TempDate) as CalendarWeekNumberAndYear,
MonthName(TempDate) as CalendarMonthAndYear,
QuarterName(TempDate) as CalendarQuarterMonthsAndYear,
// Start Dates
DayStart(TempDate) as CalendarDayStart,
WeekStart(TempDate) as CalendarWeekStart,
MonthStart(TempDate) as CalendarMonthStart,
QuarterStart(TempDate) as CalendarQuarterStart,
YearStart(TempDate) as CalendarYearStart,
// End Dates
DayEnd(TempDate) as CalendarDayEnd,
WeekEnd(TempDate) as CalendarWeekEnd,
MonthEnd(TempDate) as CalendarMonthEnd,
QuarterEnd(TempDate) as CalendarQuarterEnd,
YearEnd(TempDate) as CalendarYearEnd,
// Combo Dates
'Q' & Ceil(Month(TempDate)/3) & '/' & Year(TempDate) AS CalendarQuarterAndYear,
Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS CalendarYearAndQuarter,
'Wed ' & DayStart(WeekStart(TempDate) + 3) as CalendarWednesdays
RESIDENT TempCalendar ORDER BY TempDate ASC;
DROP TABLE TempCalendar;
LET vDateMin = Num(MakeDate(2010,1,1));
LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));
LET vDateToday = Num(Today());
LET vDateMin2 = Num(MakeDate(2010,1,1));
LET vDateMax2 = Floor(MonthEnd(Today()));
LET vDateToday2 = Num(Today());