Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Autogenerate generates 0 records

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

3 Replies
robert_mika
Master III
Master III

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)

sunny_talwar

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?

Not applicable
Author

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