Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
alextomlins
Contributor III
Contributor III

SQLTable TABLE_NAME Variable not Matching Sheet Name for Excel Load

Hi Guys, What is wrong with the below script? The variable is pulling the table names from an excel sheet using SQLTables Function. The variable seems to be fine as i can see it in the Trace statement - but when i call the variable inserted into the load statement as  ' Table is '$(vTable)')' it seems as if it can't match the Variable and the sheet in Excel. In short there is a discrepancy between SQLTables table names and the excel sheet tables names because when i come to call upon the sheets in the load it cannot identify/find them.



LIB CONNECT TO 'CRTrackerTest (924034-qlik_924034-admin)';


unqualify *;


TABLES:


SQLTables;


DATA:


LOAD 0 as Dummy AutoGenerate 0;


For i = 0 To NoOfRows('TABLES')


   Let vTable = Peek('TABLE_NAME', $(i), 'TABLES');

  

    Trace This is vTables Value: $(vTable);


    Concatenate(DATA)


    LOAD

   

    "CR Filename",

    Implemented as Date,

    Description,

    Requestor


    FROM [lib://ExcelSheets (924034-qlik_924034-admin)/CR Tracker.xls]


    (biff, embedded labels, Table is '$(vTable)')

    ;


Next

12 Replies
alextomlins
Contributor III
Contributor III
Author

I think this is where it may be going wrong. I couldn't find a driver for xlsx so I converted to an xls and placed onto server from my computer. So on the server it's XLS but i guess when i opened it on my pc it opened using excel 2013 and maybe looks like XLSX?

The problem is i don't know - very new to using excel files with multiple tabs.

I'll make the change you suggested and see if this makes a difference.

Thank you for your help

marcus_sommer
MVP & Luminary
MVP & Luminary

In regard to the driver you should ask your IT for help - they might just have blocked the access for the "normal" users.

Beside this if you already used odbc to get the sheetnames you might also use it to load the data - maybe it's easier to adjust the right sheetnames for this approach. Maybe this example is helpful in any way: Re: Xlsb File Format In qlikview.

Another possibility would be to store the sheetnames within an extra table in an extra (hidden ?) sheet within the Excel itself (this might be automated with macros) and then looping through this table to load the various sheets.

- Marcus

martinpohl
Partner
Partner

what is the "from" statement like if you select the sheet manuel?

Regards