topic Re: Loop to create QVD based on year in App Development

Loop to create QVD based on year

Brip51 — Thu, 24 Aug 2023 11:34:50 GMT

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

Re: Loop to create QVD based on year

Or — Thu, 24 Aug 2023 11:57:40 GMT

Presumably, one of the steps in the sequence that populates the year isn't correct. I'd suggest running the code in debug mode and following the steps to see where it goes wrong.

Re: Loop to create QVD based on year

anat — Thu, 24 Aug 2023 12:05:12 GMT

first can u check is any value stored in vYear variable,if yes then can u place variables in single quotes like where [Booking Year] = '$(vYear)';

Re: Loop to create QVD based on year

Brip51 — Thu, 24 Aug 2023 12:13:56 GMT

It looks like vYear does not have any values stored in it.

It will run if I add quotes - 'vYear' , but it doesn't generate the QVDs (no data)

Re: Loop to create QVD based on year

Brip51 — Thu, 24 Aug 2023 12:16:04 GMT

Thank you. When I step through, vYear is not populated so it must be something in

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

I haven't figured what is going wrong there.

Re: Loop to create QVD based on year

anat — Thu, 24 Aug 2023 12:23:19 GMT

seems below script not functioning ,can u check what $(n) output while running

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

Re: Loop to create QVD based on year

Or — Thu, 24 Aug 2023 12:28:56 GMT

The code you originally posted is:

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

Adding the -1 should be correct, but your original code doesn't seem to have that?

[Edit] That said, the issue here is the square brackets. They need to be removed.

Re: Loop to create QVD based on year

anat — Thu, 24 Aug 2023 12:26:26 GMT

try executing below code

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

Re: Loop to create QVD based on year

Brip51 — Thu, 24 Aug 2023 12:33:12 GMT

Yes it should have -1. My mistake when I posted the original code. I was testing n.

It does not work if I hard-code the middle value (n). It fails on the first value.

Re: Loop to create QVD based on year

Brip51 — Thu, 24 Aug 2023 12:36:11 GMT

Using

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

It fails with the original error

Re: Loop to create QVD based on year

Or — Thu, 24 Aug 2023 13:01:28 GMT

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

Re: Loop to create QVD based on year

marcus_sommer — Thu, 24 Aug 2023 13:04:30 GMT

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.

Re: Loop to create QVD based on year

Brip51 — Thu, 24 Aug 2023 13:11:25 GMT

I tried that-

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

same error. vYear is null.

Re: Loop to create QVD based on year

Brip51 — Thu, 24 Aug 2023 14:49:50 GMT

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

Re: Loop to create QVD based on year

marcus_sommer — Thu, 24 Aug 2023 15:36:22 GMT

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

Re: Loop to create QVD based on year

Brip51 — Thu, 24 Aug 2023 17:28:31 GMT

Hi Marcus

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

Thank You!