Loop to create QVD based on year

[Brip51]

Hi,

I am trying to create multiple QVDs from another QVD based on the Booking Year.  I have this code in my script (orginally from this post https://community.qlik.com/t5/QlikView-App-Dev/How-to-create-Year-wise-Qvd-dynamically/td-p/1637440) -

TempYear:

load Distinct [Booking Year]

resident QV_SICS_Booking;

LET NumRows=NoOfRows('TempYear');

trace $(NumRows);

For n=1 To $(NumRows)

let vYear = Peek('[Booking Year]',$(n),'TempYear');

trace $(vYear);

TempQV_SICS_Booking:

NoConcatenate

load *

resident QV_SICS_Booking 

where [Booking Year] = $(vYear);

let vFileName='QV_SICS_Booking-'& $(vYear) ;

store TempQV_SICS_Booking into D:\QVSOURCEDOCS\DEV\Source\QVD\$(vFileName).qvd (qvd);

drop table TempQV_SICS_Booking;

next n;

drop table TempYear;

I get an error I cannot resolve.  The error is :

Field not found error

Field 'a' not found

TempQV_SICS_Booking:

NoConcatenate

load *

resident QV_SICS_Booking 

where [Booking Year] = 

It seems that there is an issue with the vYear variable.  If I use a value like 2022 in place of vYear it works.

I don't understand why it is not working with the vYear variable. 

Any help or suggestions would be greatly appreciated.

Thanks,

Brian

[Or]

Remove the square brackets from Booking Year. Those are messing up the script.

[marcus_sommer · Accepted solution]

Your struggling is caused from peek() which starts with an index of 0. This means by looping from 1 to noofrows() you will skip the first value which won't result in a load-error but the max. value from noofrows() is greater as the max. possible index-value for peek() which results in NULL for peek() and this will remove the variable.

Therefore just use as iteration-logic:

for n = 0 to noofrows('table') - 1

and then you could call $(n) within the peek().

Beside this if there are any performance-issue with this data-set there would be a lot potential to optimize this slicing approach.

[Brip51]

I tried that-

let vYear =Peek('Booking Year',$(n)-1,'TempYear')

same error.  vYear is null.

[Brip51]

Thanks Marcus.  That worked.

I am not sure what you mean on this -

---

@marcus_sommer wrote:

Beside this if there are any performance-issue with this data-set there would be a lot potential to optimize this slicing approach.

---

---

Do you mean optimize before the slicing ?

Working script-

TempYear:
load Distinct [Booking Year]
resident QV_SICS_Booking;

//let vYear =Peek('Booking Year',0,'TempYear');

LET NumRows=NoOfRows('TempYear');
trace $(NumRows);
For n=0 To $(NumRows)-1

let vYear=Peek('Booking Year',$(n),'TempYear') ;
//
//2022
trace $(vYear);
TempQV_SICS_Booking:
NoConcatenate
load *
resident QV_SICS_Booking
where [Booking Year] = $(vYear);

let vFileName='QV_SICS_Booking-'& $(vYear) ;
store TempQV_SICS_Booking into D:\QVSOURCEDOCS\DEV\Source\QVD\$(vFileName).qvd (qvd);
drop table TempQV_SICS_Booking;
next n;
drop table TempYear;

Thanks agian,

Brian

[marcus_sommer]

Optimizing means doing something like this:

t1: load num(fieldvalue('Booking Year', recno())) as X autogenerate fieldvaluecount('Booking Year');
drop tables QV_SICS_Booking;

for i = 1 to fieldvaluecount('X')
   t2: load num(fieldvalue('X', $(i))) as [Booking Year] autogenerate 1;
   t3: load * from QV_SICS_Booking.qvd (qvd) where exists([Booking Year]);
   store t3 into QV_SICS_Booking_$(i).qvd (qvd); drop tables t2, t3;
next
drop tables t1;

respectively as description:

• not to load resident to read the available values of a table else to load this information from the system-table which has just the distinct field-values
• to drop the resident table to release the RAM
• then in the loop writing each single value into a field
• which is used as exists() condition to keep the qvd-loading optimized
• storing the chunk and dropping the temp-tables

[Brip51]

Hi Marcus

This is great info.  I did not even think to think of this...

Thank You!