Using Excel File for Variables in Qlikview says File not found

Question

In my company we are using an excel file to store our expressions and variables so we can update them in one central location and we use multiple tabs (in qlikview tables) to keep them organized. I didn't like the duplication of code, so I created a sub procedure and then just call it with the variables that change. the syntax looks correct but it keeps giving me a script Error that says:

Field not Found = <,>

VariableList:

Load ,

From [D:\Development\UserDocs\ExpressionDictionary.xlsx] (ooxml, embedded labels, table is $(ExcelTable)) WHERE [Load Flag];

sub GetVariables(TableName, ExcelTable, NameColumn, VariableColumn)
    
        $(TableName):
        
        LOAD 
             $(NameColumn), 
             $(ExpressionColumn)
        FROM [D:\Development\UserDocs\ExpressionDictionary.xlsx] (ooxml, embedded labels, table is $(ExcelTable))
        WHERE [Load Flag];
        
        FOR counter = 1 TO NoOfRows('$(TableName)')
            LET vVariable = Peek('$(NameColumn)', $(counter) -1, '$(TableName)');
            LET $(vVariable) = Peek('$(ExpressionColumn)', $(counter) -1, '$(TableName)');
        NEXT 
        
        LET vVariable = null();
        LET counter = null();
    
end sub

call GetVariables('VariableList', 'Variables') 

I understand that it doesn't see the column Names but I don't know how to get it to work. I have tried both with and without ' surrounding the dollar expansion in the Load script. What am I missing?

Answer 1

The GetVariables function you have there takes 4 parameters - TableName, ExcelTable, NameColumn and VariableColumn.

When you've called it, you've only specified the first 2, so NameColumn and Variable Column are blank. To fix it you need to tell it what the column names from the Variables sheet in Excel you want it to load. Could be "VariableName" and "VariableValue" but I can't say without seeing the file itself.

Something like:

call GetVariables('VariableList','Variables','VariableName','VariableValue')

The error is because it's running this code:

VariableList:
LOAD 
    , 

FROM [D:\Development\UserDocs\ExpressionDictionary.xlsx] 
(ooxml, embedded labels, table is Variables)
WHERE [Load Flag];

Note that there are no field names in the LOAD statement as these variable values are not defined.