topic Re: No numeric FOR_NEXT in QlikView

No numeric FOR_NEXT

Tue, 10 Jun 2014 16:05:19 GMT

Hi,

I have this piece of script which works properly for the HOTEL='BAK'. But I would like do the same for other no numeric values of 'HOTEL' and I do not know how to make a for...next command when the variable to consider is a text.

Any idea please?

In addition, how to capture (in the script) into a variable the maximun value of 'INVENTARIO_BEP' ? .

I tried 'LET INVENT= MAX (INVENTARIO_BEP)' but it did not work.

Thanks.

DATOS:
LOAD
HOTEL,
FIJOS,
VARIABLES/HOCU_BEP AS VAR_UNIT,
INVENTARIO_BEP
FROM DATOS(QVD) WHERE HOTEL='BAK';
RENAME TABLE TEMP TO DATOS;

FOR tarifa=0 to 400;
FOR ocupadas=0 to 630 step 10;
TEMP:
LOAD
HOTEL,
FIJOS/30 AS FIJOS,
VAR_UNIT*$(ocupadas) AS VARIABLES,
$(tarifa)*$(ocupadas) AS INGRESOS,
ROUND($(ocupadas)/630*100,1) AS OCUPACION,
$(tarifa) AS TARIFA,
ROUND($(tarifa)*$(ocupadas)-FIJOS/30-VAR_UNIT*$(ocupadas)) AS MARGEN
RESIDENT DATOS;
NEXT ocupadas;
NEXT tarifa;

DROP TABLE DATOS;
RENAME TABLE TEMP TO DATOS;

Re: No numeric FOR_NEXT

alexandros17 — Tue, 10 Jun 2014 16:16:30 GMT

Take a look to the for each instruction ...

Re: No numeric FOR_NEXT

swuehl — Tue, 10 Jun 2014 21:18:40 GMT

Yes, look into FOR EACH .... NEXT in the Help:

"The for each..next control statement is a script iteration construct which executes one or several statements for each value in a comma separated list. The statements inside the loop enclosed by for and next will be executed for each value of the list. Special syntax makes it possible to generate lists with file and directory names in the current directory.

The syntax is:

for each var in list

[statements]

[exit for [ ( when | unless ) condition ]

[statements]

next[var]"

[continued...]

For your second request, you would need to find the maximum in a resident table load, either by sorting the field or using max():

MAX:

LOAD

max(INVENTARIO_BEP) as MaxIB

RESIDENT YourTable;

LET INVENT= Peek('MaxIB', 0, 'MAX');

Re: No numeric FOR_NEXT

Wed, 11 Jun 2014 08:58:22 GMT

Hi , I modified my script like this:

FOR EACH H IN 'BAK','BPT'

TEMP:
LOAD
HOTEL,
FIJOS,
VARIABLES/HOCU_BEP AS VAR_UNIT,
INVENTARIO_BEP
FROM DATOS(QVD) WHERE HOTEL=$(H);

etc...

It gives me the following error message, and I do not understand why.

Field not found - <BAK>

TEMP:

LOAD

HOTEL,

FIJOS,

VARIABLES/HOCU_BEP AS VAR_UNIT,

INVENTARIO_BEP

FROM DATOS(QVD) WHERE HOTEL=BAK

Re: No numeric FOR_NEXT

tresB — Wed, 11 Jun 2014 09:02:57 GMT

Try putting single quotes around dollar expansion, like:

FROM DATOS(QVD) WHERE HOTEL= '$(H)';

Wait, I guess your for each loop would not be required here. You can do it probably in a simpler way using match(), like:

Load

...

FROM DATOS(QVD) WHERE Match(HOTEL , 'BAK', 'BPT' );

Re: No numeric FOR_NEXT

jagan — Wed, 11 Jun 2014 09:22:14 GMT

Hi,

In this scenario you don't need a For loop, try like this without loop

TEMP:

LOAD

HOTEL,

FIJOS,

VARIABLES/HOCU_BEP AS VAR_UNIT,

INVENTARIO_BEP

FROM DATOS(QVD) WHERE Match(HOTEL, 'BAK','BPT');

Regards,

jagan.

Re: No numeric FOR_NEXT

Wed, 11 Jun 2014 10:03:00 GMT

For some reason , when using

LET INVENT=PEEK('INVENTARIO_BEP',0,'DATOS');

each...for works all right instead of match.

Thank you everybody.