Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a function that receive as a parameter a string.
Within this function I have a for loop. I have several problems:
1) In first iteration when i=2 in for loop, autogenerate seems to not works, debugging says 0 records added to [$(kpi_targets_table)].
Autogenerate in my case should generate dates in range 01/01/2014 to 01/04/2014 in first iteration (i=2).
So after the for loop below variable is getting 0:
LET vRowCursor = NoOfRows('$(kpi_targets_table)');
Also, after the for loop below variable vDate_prev is getting date as a float number instead of date in format DD/MM/YYYY:
(vDate contains for example 01/01/2014)
LET vDate_prev = $(vDate);
Why?
SUB CalendarFromField(_name)
let ordered_kpi_table = 'Ordered_KPI_' & '$(_name)';
let temp_kpi_table = 'TEMP_KPI_' & '$(_name)';
let kpi_targets_table = 'KPI_Targets_' & '$(_name)';
[$(ordered_kpi_table)]:
noconcatenate LOAD
*
Resident [$(temp_kpi_table)]
Order By Date;
LET vRowCount = NoOfRows('$(ordered_kpi_table)');
SET vRowCursor = 0;
LET vDate_prev = PEEK('Date',0,'$(ordered_kpi_table)');
LET vKpi1_prev = PEEK('KPI_Min,0,'$(ordered_kpi_table)');
LET vKpi2_prev = PEEK('KPI_Max',0,'$(ordered_kpi_table)');
FOR i = 2 TO $(vRowCount)
LET vDate = PEEK('Date',$(i)-1,'$(ordered_kpi_table)');
LET vKpi1 = PEEK('KPI_Min',$(i)-1,'$(ordered_kpi_table)');
LET vKpi2 = PEEK('KPI_Max',$(i)-1,'$(ordered_kpi_table)');
[$(kpi_targets_table)]:
Load DATE($(vDate_prev)+ROWNO()-$(vRowCursor)-1,'DD/MM/YYYY') as Date,
$(vKpi1_prev) as KPI_Min,
$(vKpi2_prev) as KPI_Max
AutoGenerate($(vDate)-$(vDate_prev)+1);
LET vRowCursor = NoOfRows('$(kpi_targets_table)');
LET vDate_prev = $(vDate);
LET vKpi1_prev = $(vKpi1);
LET vKpi2_prev = $(vKpi2);
NEXT i
END SUB
Is hard to say without your app but...
Use Debug and see what are you getting for:
AutoGenerate($(vDate)-$(vDate_prev)+1);
if 0 then you have to change or format or your calculations,
Feeling Qlikngry?
What exactly are you trying to do here? May be there is an easier way to do this. Can you provide a sample with the expected output you are trying to get to?
Initialy I was trying to do what you explained to me in another post:
Iterate over date table to autogenerate missing dates with values associated
I was doing this before you provide me a solution in the indicated link. So as your solution is very simple, easier, readable and cleaner, I decided to use your solution.
This is another way to do it, but more complicated and more code needed to do the same. I found this in the following link:
Add rows for days in between | Qlik Community
and I was trying to adapt it to my case.
In this concrete case I don't know what was happening.... but autogenerate was returning 0 records.
Many thanks again Sunny for all your support