Cannot drop table // Is the table in fact even created?

[#1]

Dears,

I'm facing a quite stupid problem... and I suppose the solution is out there somewhere, so here I go.

I have a loop which creates a temporary table based on another table (resident). Unfortunately the table cannot be dropped at the end of every iteration. When taking a closer look at the debugger it even looks like the table hasn't been created in the first place. Here's the code I'm running.

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

// ************
// Main program
// ************

//Build table list based on flat file
TableList:
LOAD @1 as [Table Name],
 @2 as _table_Id
FROM
[Qlikview Table List.txt]
(txt, codepage is 1252, no labels, delimiter is ' ', msq);

//Load SiteAttributes from QVD
SiteAttributesListing:
LOAD
 satt_Id,
 satt_Name,
 satt_Table
FROM
[GP_SITEATTRIBUTE.qvd]
(qvd);


//Loop all the GLIMS tables based on TableList table
for aRow = 1 to NoOfRows('TableList')//-1

 //For each row get tablename(Fist column)/id(Second column) value
 let aTableName=FieldValue(FieldName(1,'TableList'),aRow);
 let aTableId=FieldValue(FieldName(2,'TableList'),aRow);

 //Retrieve all site attributes for selected table
 aTableSattList:
 LOAD
 satt_Id,
 satt_Name,
 satt_Table
 Resident SiteAttributesListing
 where satt_Table = '$(aTableId)';

 //perform actions

 drop tables aTableSattList;


next

Thanks in advance!

Kind regards,

Maarten

[Anonymous]

What happens when you drop the " drop tables aTableSattList;" ?
Does it create the aTableSattList?

[#3]

Hello Maarten,

did you check the contents of the variables within the for-loop, after the let-statements:

let aTableId=FieldValue(FieldName(2,'TableList'),aRow);
TRACE aTableId = $(aTableId)

HtH

Roland

[chris_johnson]

Hi Martin,

I think I had a similar problem a few weeks ago. Try putting NOCONCATENATE (might have spelt that wrong!) before your LOAD for the aTableSattList.

I think QlikView looks at the columns in the table, sees that the same columns are in a previous table and loads them into the previous table even though you've specified a table name. So I think you're right in that it might not even be created.

Give it a whirl and see if you get a different result.

Regards,

Chris

[#5]

Dear all,

Thanks for helping me out this quickly!!!

In fact the table could not be created. And I just figured out why...

Apparently the debugger does not notify when you re-use similar field names, which happened when creating the temporary table.

This is how the script should have been. (Problem resolved).

 //Retrieve all site attributes for selected table
 aFilteredSattList:
 LOAD
 satt_Id as _satt_Id,
 satt_Name as _satt_Name,
 satt_Table as _satt_Table
 Resident SiteAttributesListing
 where satt_Table = '$(aTableId)';

Giving the problem some well-deserved rest solved it 🙂

[chris_johnson]

Hi Marten (sorry, spelt your name wrong earlier),

Yes, now you mention it I think this is how I ended up solving it too. Someone else suggested to me using NOCONCATENATE so I guess this is probably the same sort of thing.

Regards,

Chris

[#7]

Hi Chris,

That is correct 🙂

Ps: In fact, you spelled my name correctly . It was me, who was so stupid to make a typo when registering at the forum

Have a nice day!