topic Re: Scripting error qvds in QlikView

Scripting error qvds

Thu, 07 May 2015 08:40:10 GMT

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;

Re: Scripting error qvds

sunny_talwar — Thu, 07 May 2015 08:47:10 GMT

May be your MinPeriod and MaxPeriod are not numeric values. Try may be this:

Noconcatenate TempMinMax:

LOAD Num(FirstValue(HPeriod)) as MinPeriod,

Num(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;

Re: Scripting error qvds

Gabriel — Thu, 07 May 2015 09:03:16 GMT

Hi,

I think the logic need to be different here. I will look to use FOR EACH Loop

Re: Scripting error qvds

Thu, 07 May 2015 12:54:57 GMT

Can you give me an example?

On Thu, May 7, 2015 at 11:03 AM, Gabriel Oluwaseye <qcwebmaster@qlikview.com

Re: Scripting error qvds

Thu, 07 May 2015 12:56:38 GMT

I am still getting

Script line error:

FOR vPeriod = to

Re: Scripting error qvds

Gabriel — Thu, 07 May 2015 15:13:57 GMT

Hi,

Are you using calendar script to build this?

My reason for asking is, you can still use For Loop.

You just have to have a Data Island of all the dates between Min and Max of your date, then use For Loop.

Re: Scripting error qvds

vincent_ardiet — Thu, 07 May 2015 15:17:01 GMT

Try with quotes in your peek expression like this : LET vPeriodMin = Peek('MinPeriod');

Re: Scripting error qvds

Fri, 08 May 2015 06:47:22 GMT

I built up a table from a resident load from the original table. Note

though the field is a Financialperiod, so 201501, 201502 etc. There are no

other date fields in use in this table

On Thu, May 7, 2015 at 5:14 PM, Gabriel Oluwaseye <qcwebmaster@qlikview.com>

Re: Scripting error qvds

Gabriel — Fri, 08 May 2015 10:22:10 GMT

Hi,

Please try to understand the script below and implement it.

I have tried to include notes where I feel clarification is needed.

LET vMin = FLoor(MakeDate(2015,04,20));

LET vMax = FLoor(MakeDate(2015,04,30));

LET vDiff = vMax - vMin + 1; // Plus 1 here ensure we have to full range of date differerence

DateIsland:
LOAD
$(vMin) + RecNo() -1 AS Date //Minus 1 here ensures that the date starts from the vMin date in our loop
AutoGenerate ($(vDiff));
//Load full date range
FOR i = 0 TO NoOfRows('DateIsland')
LET vQVDDate = Date(Peek('Date',$(i),'DateIsland'),'YYYY-MM-DD');
TRACE $(vQVDDate);
//Here include your store procedure scripts
NEXT i;

Re: Scripting error qvds

Fri, 08 May 2015 11:02:59 GMT

Thanks Gabriel

I think I understand what your script does, the concern I have is still

that I do not have full dates, I only have one date field which is not a

fulldate, the field comes through from the database as YYYYMM, that is all

I have. This originally created the issue that I had that it created files

for non existant months, like month 13, 14, because it was a numeric, so

when it got to December 2014 it would create a 201413 after December and

just carried on with the months. The date range one you gave is really

going to help with later scripting on other models, but for this one with

only Financial periods, I am just not getting it

I have changed the script to a for each loop. It now creates files, but

only for the last month

Noconcatenate TempPeriod:

LOAD HPeriod as Period

Resident HISTORYAGEING

order by HPeriod;

for each Period in TempPeriod

NoConcatenate

TempData:

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

;

STORE TempData INTO HISTORYAGEING_$(vPeriod).qvd (qvd);

DROP TABLE TempData;

On Fri, May 8, 2015 at 12:23 PM, Gabriel Oluwaseye <qcwebmaster@qlikview.com

Re: Scripting error qvds

vincent_ardiet — Fri, 08 May 2015 11:31:58 GMT

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

LET vPeriodMin = Peek('MinPeriod');

Re: Scripting error qvds

Fri, 08 May 2015 12:02:22 GMT

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;

Re: Scripting error qvds

sunny_talwar — Fri, 08 May 2015 12:06:42 GMT

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.

Re: Scripting error qvds

Peter_Cammaert — Fri, 08 May 2015 12:17:23 GMT

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...

Re: Scripting error qvds

Anonymous — Fri, 08 May 2015 12:23:03 GMT

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;

Edit: a few fixes

Re: Scripting error qvds

Fri, 08 May 2015 12:43:36 GMT

The SQL type is an integer

Re: Scripting error qvds

Fri, 08 May 2015 13:45:24 GMT

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;

Re: Scripting error qvds

Anonymous — Fri, 08 May 2015 13:54:28 GMT

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.

Re: Scripting error qvds

vincent_ardiet — Fri, 08 May 2015 13:57:14 GMT

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

Re: Scripting error qvds

Mon, 11 May 2015 10:34:22 GMT

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