topic Re: Autogenerate generates 0 records in QlikView

Autogenerate generates 0 records

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Autogenerate generates 0 records

robert_mika — Sun, 31 Jul 2016 19:46:25 GMT

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?

How To /Missing Manual(20+ articles)

Re: Autogenerate generates 0 records

sunny_talwar — Sun, 31 Jul 2016 20:23:09 GMT

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?

Re: Autogenerate generates 0 records

Mon, 01 Aug 2016 19:01:49 GMT

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