Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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
Anil_Babu_Samineni

Alex Tomlins wrote:

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

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

I can think, Problem May coming here. can you share screenshot of error?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
marcus_sommer

Try it with:

...

(biff, embedded labels, Table is [$(vTable)]);

- Marcus

alextomlins
Contributor III
Contributor III
Author

Didn't change error unfortunately - I get the first alphabetical sheet in the SQL TABLE List. So April is not the left most but it is the first row in the SQLTable 'TABLES' Table....QLIK ERROR.PNG

marcus_sommer

The single-quotes around the sheetname are wrong.

- Marcus

stigchel
Partner - Master
Partner - Master

And you probably need to remove the $

Let vTable = purgeChar(purgeChar(Peek('TABLE_NAME', $(i), 'TABLES'), chr(39)), chr(36));

alextomlins
Contributor III
Contributor III
Author

Still Getting an Error - So confusing - now i have;

Let vTable = purgeChar(purgeChar(Peek('TABLE_NAME', $(i), 'TABLES'), chr(39)), chr(36))

It looks like the variable and the data load Variable call match - what is going on lol.

I do notice that SQLTables is pulling an empty duplicate ending in an underscore??

QLIK ERROR.PNG

These are tabs in the excel Sheet - It seems to match Apr 2018. I know that there are hidden sheets

gggg.PNG

The SQL Tables is kicking out the following;

dddd.PNG

stigchel
Partner - Master
Partner - Master

I Don't use Sense but qlikview, normally the same but maybe I'm missing something. If so maybe others can help you..

One suggestion maybe, do you have something like a file wizard that generates the load script for a file? Maybe you can try to load just this one tab Apr 2018 using a wizard and see if and what the differences are in syntax.

alextomlins
Contributor III
Contributor III
Author

There is a wizard yes thank you! Interestingly it is with all sorts Dollars, single quotes and underscores. This must be the problem. which means SQLTables is pulling the data out exactly as the ODBC connection reads the sheet names. i will remove the purge character and see if that works

dddd.PNG

marcus_sommer

Your sheet-screenshot of the tabs looked like a xlsx and not like a xls and in this case the fileformat with biff would be wrong and needs to be changed to ooxml.

- Marcus