topic Re: Variables Not Working In A For Loop in QlikView

Variables Not Working In A For Loop

Tue, 26 Jul 2016 19:24:36 GMT

I have been trying to loop through a collection of qvd files to extract field data from them. However, I keep getting the following error: "Syntax error, missing/misplaced FROM: QVD_METADATA:" In the script body displayed in the error message, I also see that the variables have vanished from the places where I placed them in the script (not in the script itself, just in the script body displayed in the error message).

It appears the variables are not being created or assigned values. But, I can't see where the problem lies. Does anyone have any experience with this? Here is the code I am using:

QVD_DATA:

LOAD

, Date(Today(), 'DD-MMM-YYYY') AS UPDATE_DATE_QVD_METADATASET

;

LOAD

[QV File Name],

[QVD Full Path]

FROM

$(vInputFile1)

(ooxml, embedded labels, table is [Data Links]);

Let vRowNum = NoOfRows('QVD_DATA'); /*Get total number of rows for the loop to do its thing*/

For i = 1 To $(vRowNum)

Let vQvdPath = FieldValue([QVD Full Path],$(i)); /*Assign each qvd file path to a variable to be used as the path in the metadata extraction load*/

Let vFileName = FieldValue([QV File Name],$(i));

QVD_METADATA:

LOAD

1 as FieldCount,

$(vFileName) as [File Name],

FieldName as [Field Name],

BitOffset as [Bit Offset],

BitWidth as [Bit Width],

Bias as [Bias],

NoOfSymbols as [No Of Symbols],

[NumberFormat/Type] as Type,

[NumberFormat/nDec] as Dec,

[NumberFormat/UseThou] as UseThou

FROM $(vQvdPath) (XmlSimple, Table is [QvdTableHeader/Fields/QvdFieldHeader]);

Next i

Re: Variables Not Working In A For Loop

jsakalis — Tue, 26 Jul 2016 19:49:38 GMT

Hi Eugene,

Try putting quotes around $(vFileName): '$(vFileName)' as [File Name],

Re: Variables Not Working In A For Loop

cwolf — Tue, 26 Jul 2016 19:53:42 GMT

There will be spaces in the $(vQvdPath) and $(vFileName) has to be in quotes. Try this:

QVD_METADATA:

LOAD

1 as FieldCount,

'$(vFileName)' as [File Name],

FieldName as [Field Name],

BitOffset as [Bit Offset],

BitWidth as [Bit Width],

Bias as [Bias],

NoOfSymbols as [No Of Symbols],

[NumberFormat/Type] as Type,

[NumberFormat/nDec] as Dec,

[NumberFormat/UseThou] as UseThou

FROM [$(vQvdPath)] (XmlSimple, Table is [QvdTableHeader/Fields/QvdFieldHeader]);

Re: Variables Not Working In A For Loop

vinieme12 — Tue, 26 Jul 2016 20:06:15 GMT

Debug your script to see which one of the variables isn't evaluating

Re: Variables Not Working In A For Loop

Tue, 26 Jul 2016 20:11:32 GMT

Thank you, John and Christian, those are good suggestions. However, the same error keeps getting thrown. It has something to do with the variables not getting evaluated, because they are not being created or initialized. If, after execution, I go and look at the variables using 'Settings/Variable Overview', the vRowNum variable is there. But, the vFileName and vQvdPath variables are not there.

Re: Variables Not Working In A For Loop

Tue, 26 Jul 2016 20:19:45 GMT

The vQvdPath and vFileName variables are not assigned any values, and the [QV File Name] and [QV Full Path] fields from the QVD_DATA load at the start of the script also have NULL values. Something isn't loading right in the Load portion of the For loop.

Re: Variables Not Working In A For Loop

Peter_Cammaert — Tue, 26 Jul 2016 20:25:03 GMT

Well, it's quotes, quotes and some more quotes. You're nearly there.

The two variables that aren't being created appear in a LET assignment with function FieldValue. Those don't seem to be working, correct? You specify the field content as first parameter, but the QV Desktop help carries an example of how this function should be called:

See? The field name should be specified as a string. Quotes. Damn quotes...

Good luck,

Peter

Re: Variables Not Working In A For Loop

vinieme12 — Tue, 26 Jul 2016 20:33:13 GMT

What is vRowNum evaluating to, does your code even enter the for loop?

Re: Variables Not Working In A For Loop

vinieme12 — Tue, 26 Jul 2016 20:36:41 GMT

on the top right corner of the screen , below file menu Click on Debug and Step into your code line by line to check

Re: Variables Not Working In A For Loop

Tue, 26 Jul 2016 20:40:40 GMT

vRowNum evaluates to 1347, which is the correct number of rows. vQvdPath and vFileName evaluate to NULL. After these are evaluated the script error is then thrown.

Re: Variables Not Working In A For Loop

Tue, 26 Jul 2016 20:41:25 GMT

Thanks for the suggestion, Peter. I tried adding the quotes as suggested, but no dice. The script error is still thrown and the two variables are still evaluating to null when I step through the script.

Re: Variables Not Working In A For Loop

Peter_Cammaert — Tue, 26 Jul 2016 21:01:26 GMT

Did you remove the square brackets from the field names?

The error message is generated because the $(vFileName) value for column [File Name] evaluates to nothing, and an illegal LOAD Column definition syntax will almost always lead to a message about a missing FROM clause. The same happens when you put one comma too many at the end of the column list.or you forget an AS keyword.

Best,

Peter

Re: Variables Not Working In A For Loop

vinieme12 — Tue, 26 Jul 2016 21:11:36 GMT

try using peek() instead of fieldvalue

peek(fieldname [ , row [ , tablename ] ] )

Re: Variables Not Working In A For Loop

vinieme12 — Tue, 26 Jul 2016 21:22:25 GMT

example:

Let vQvdPath = PEEK('QVD Full Path',$(i),'QVD_DATA');

Let vFileName = PEEK('QV File Name',$(i),'QVD_DATA');

Re: Variables Not Working In A For Loop

Peter_Cammaert — Tue, 26 Jul 2016 21:23:12 GMT

Good thinking by Vineeth.

FieldValue returns Distinct values, so there will probably be fewer than the number of table rows. If all QVD files reside in the same path, there will be only one FieldValue for field vQvdPath.

See the same help text from above.

Best,

Peter

Re: Variables Not Working In A For Loop

Wed, 27 Jul 2016 19:08:05 GMT

Thanks so much for your help, Vineeth and Peter! Ultimately, I had to redo the script quite a bit and adopt a different approach. However, now it works! Here is how the code looks, in case anyone needs to extract metadata from QVDs and might find this useful:

QVD_DATA:

LOAD

, Date(Today(), 'DD-MMM-YYYY') AS UPDATE_DATE_QVD_METADATASET

;

LOAD

[QV File Name],

[QVD Full Path],

RecNo() AS [Data Row]

FROM

$(vInputFile1)

(ooxml, embedded labels, table is [Data Links])

;

NoConcatenate

QVD_END:

LOAD Distinct

MAX([Data Row]) AS [Data Row]

Resident QVD_DATA

;

Let vNumberQVD=Num(Peek('Data Row',0,'QVD_END')); /*Get total number of rows for the loop to do its thing*/

Let vNumberStart=1;

Drop Table QVD_END;

Do While vNumberStart<=vNumberQVD

NoConcatenate

PATH:

Load

Resident QVD_DATA

Where [Data Row]=$(vNumberStart)

;

Let vPath='['&Peek('QVD Full Path',0,'PATH')&'] ';

Let vFileName=Peek('QV File Name',0,'PATH');

Drop Table PATH;

Let vNumberStart=$(vNumberStart)+1;

// Let vQvdPath = FieldValue('[QVD Full Path]',$(i)); /*Assign each qvd file path to a variable to be used as the path in the metadata extraction load*/

// Let vFileName = FieldValue('[QV File Name]',$(i));

QVD_METADATA:

LOAD

1 as FieldCount,

'$(vFileName)' as [QV File Name],

FieldName as [Field Name],

BitOffset as [Bit Offset],

BitWidth as [Bit Width],

Bias as [Bias],

NoOfSymbols as [No Of Symbols],

[NumberFormat/Type] as Type,

[NumberFormat/nDec] as Dec,

[NumberFormat/UseThou] as UseThou

FROM $(vPath)(XmlSimple, Table is [QvdTableHeader/Fields/QvdFieldHeader])

;

QVD_TABLE_INFO:

LOAD

1 as QVDCount,

'$(vFileName)' as [File Name],

QvBuildNo as [QV Build No],

CreatorDoc as [QVD Creator],

CreateUtcTime as [Time Created],

SourceFileSize as [Source File Size],

TableName as [Table Name],

RecordByteSize as [Record Byte Size],

NoOfRecords as [Number Of Records]

FROM $(vPath) (XmlSimple, Table is [QvdTableHeader]);

Loop;

Re: Variables Not Working In A For Loop

Wed, 27 Jul 2016 19:17:35 GMT

Actually, the QVD_TABLE_INFO load should be removed from that code above. A Cartesian join results otherwise.