Scripting error qvds

[#1]

Hi

I have been struggling for a while with qvd's

I am receiving this error when running this code, what am I missing here.

I am using the Text function to try to change the data type as I was getting qvd's created for 201413, 201414 etc, I only want a file for every actual Period which runs from 2003 - 01 through 12, then 2014-01 to 12

Script line error:

FOR vPeriod =  to

Noconcatenate TempPeriod:

LOAD distinct Text(HPeriod) as Period

Resident HISTORYAGEING

order by HPeriod;

Noconcatenate TempMinMax:

LOAD FirstValue(HPeriod) as MinPeriod,

LastValue(HPeriod) as MaxPeriod

Resident HISTORYAGEING;

LET vPeriodMin = Peek(MinPeriod);

LET vPeriodMax = Peek(MaxPeriod);

FOR vPeriod = $(vPeriodMin) to $(vPeriodMax)

NoConcatenate

TempData:

LOAD * Resident HISTORYAGEING where HPeriod = $(vPeriod);

STORE TempData into HISTORYAGEING_$(vPeriod).qvd;

Drop table TempData;

NEXT

[vincent_ardiet]

Have you tried what I suggested in my previous post? Using quotes in your peek?

LET vPeriodMin = Peek('MinPeriod');

[#3]

I tried this now with the inverted commas, I am still getting non existing months created between 201412 and 201501, that would be because the field is numbers.

Vincent, I am not sure where your specific suggestion is, is what i did basically right, and I am just missing something small somewhere? I am going to have a relook for now at sunindia's suggestion, and Gabril's as well, think I understand better  what he is getting at as well after spending more time looking at his code

Noconcatenate TempPeriod:

LOAD distinct Text(HPeriod) as Period

Resident HISTORYAGEING

order by HPeriod;

Noconcatenate TempMinMax:

LOAD FirstValue(HPeriod) as MinPeriod,

LastValue(HPeriod) as MaxPeriod

Resident HISTORYAGEING;

LET vPeriodMin = Peek('MinPeriod');

LET vPeriodMax = Peek('MaxPeriod');

FOR vPeriod = $(vPeriodMin) to $(vPeriodMax)

NoConcatenate

TempData:

LOAD * Resident HISTORYAGEING where HPeriod = $(vPeriod);

STORE TempData into HISTORYAGEING_$(vPeriod).qvd;

Drop table TempData;

NEXT

[sunny_talwar]

Lets start this from beginning. What format is HPeriod stored in the database? To me the format of HPeriod may have something to do with all the issues that you are getting.

[Peter_Cammaert]

Vincent is right. Peek parameters FieldName and TableName must be quoted, or the peek function will return...nothing. That's why the line

FOR  vPeriod = $(vPeriodMin) to $(vPeriodMax)

expands to

FOR vPeriod = to

Two times nothing results in an illegal FOR statement...

[Anonymous]

The noconcatenate is in a wrong place and not needed.  Try this:

TempPeriod:
LOAD distinct Period
Resident HISTORYAGEING
order by HPeriod;

LET vPeriodMin = Peek('Period',0,'TempPeriod');
LET vPeriodMax = Peek('Period',-1, 'TempPeriod');

DROP TABLE TempPeriod;

FOR vPeriod = $(vPeriodMin) to $(vPeriodMax)

TempData:
LOAD * Resident HISTORYAGEING where HPeriod = $(vPeriod);
STORE TempData into HISTORYAGEING_$(vPeriod).qvd;
Drop table TempData;

NEXT

Edit: a few fixes

[#7]

The SQL type is an integer

[#8]

Thanks Michael

This is the script I have now, had to put noconcatenate back on the one r it added one row to the original table,the problem now is I have data for 201307 to201408 on the model, I commented out TempPeriod, and it is showing all the Periods, though, when exporting to qvd it only creates 201307 and then 201308, nothing else, that says to me that something may be wrong in the ordering where it gets Peek('Period',-1, 'TempPeriod'), but it is not making sense to me at all why it is doing it

TempPeriod:

LOAD distinct Period

Resident HISTORYAGEING

order by HPeriod;

LET vPeriodMin = Peek('Period',0,'TempPeriod');

LET vPeriodMax = Peek('Period',-1, 'TempPeriod');

DROP TABLE TempPeriod;

FOR vPeriod = $(vPeriodMin) to $(vPeriodMax)

TempData:

LOAD * Resident HISTORYAGEING where HPeriod = $(vPeriod);

STORE TempData into HISTORYAGEING_$(vPeriod).qvd;

Drop table TempData;

NEXT

[Anonymous]

Here is where the troubleshooting starts...

1. Check what the variables' values are.

2. If they're incorrect, comment the script after loading the TempPeriod, and check what is in TempPeriod.  You probably need to rename the field there, because it also exists in the other table and it is harder to find what exactly in the TempPeriod table.

[vincent_ardiet]

you can also try to use the debug mode in order to follow step by step your loop

[#11]

The table field has all the correct values, I am aliasing it, in the

original table it is HPeriod, in the TempTable it is Period. It comes

through from SQL as an interger.

When I check the variable values the min value is 200307 and the maxValue

is 200308 where it should be 200408, not really making sense, as if I order

the values by loadorder they still show from 201307 through 201408. Any

idea why peek -1is wrong if the load order seems to be correct. I also have

a order by in the Load statement

On Fri, May 8, 2015 at 3:55 PM, Michael Solomovich <qcwebmaster@qlikview.com