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]

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.

[anat]

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)';

[Brip51]

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)

[Brip51]

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.

[anat]

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

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

[Or]

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.

[anat]

try executing below code

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

[Brip51]

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.

[Brip51]

Using

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

It fails with the original error