topic Re: Issue with some temporary tables being created in QlikView

Issue with some temporary tables being created

datanibbler — Tue, 14 Oct 2014 10:39:50 GMT

Hi,

I have an issue, seemingly in one piece of my code that goes like this:

SOFA_Liste_v2:
LOAD
*
From_Field(Temp1, Mat_unsauber)
(no labels);
// At this point, there should be four records in this table
RENAME FIELD @1 TO Sachnr_und_Anz_$(v_line2);

...

DROP TABLE SOFA_Liste_v2;

At this point (this is the second one of two subroutines being called), there is an error "FieldName already exists", the reason being that there is some table created in an earlier iteration of that subroutine called "SOFA_Liste_v2-1".

I would like to know why that is being created and not dropped and if I can avoid the creation of that clone - or at least delete it.

Thanks a lot!

Best regards,

DataNibbler

Re: Issue with some temporary tables being created

datanibbler — Tue, 14 Oct 2014 12:27:30 GMT

Strangely, those temporary tables seem to be created only when processing certain lists, not always.

A simple >DROP TABLE < statement will not solve this issue therefore.

Re: Issue with some temporary tables being created

datanibbler — Tue, 14 Oct 2014 12:36:21 GMT

Is there any way to just delete any table that might be in the QlikView_database?

I could then put that in a condition to check the nr. of tables

Re: Issue with some temporary tables being created

dickelsa — Tue, 14 Oct 2014 12:40:57 GMT

Hi,

Maybe i am misunderstanding the question,but why dont you use the ' drop table' syntax? Please attach script or script part for better understanding the problem

Regards

Dick

Re: Issue with some temporary tables being created

datanibbler — Tue, 14 Oct 2014 12:47:46 GMT

Hi dick,

unfortunately I can't - the script is very complex.

It runs over six sheets - and those tables seem to be generated only when processing certain lists, not with the others.,

The piecé of code where I suppose this happens is:

SOFA_Liste_v2:
LOAD
*
From_Field(Temp1, Mat_unsauber)
(no labels);
RENAME FIELD @1 TO Sachnr_und_Anz_$(v_line2);

(In that field, there are actually four records (in one row in the base Excel_list), so the output should be 4 records. That works well - it's only in one of those six lists that these temporary tables seem to be created.

Just a moment, I'll let the whole thing run once more and we'll see.

Re: Issue with some temporary tables being created

datanibbler — Tue, 14 Oct 2014 13:05:32 GMT

Hi,

yes, I am pretty sure now.

Of the six lists I am processing, those file_fragments are created on only one.

BUT - this is very confusing. In that list, there are only three completely unusable records and eight orderly ones - the unusable ones have been cleaned out by the first subroutine, so there should be none left for the second subroutine ...

I'm afraid I can't explain this clearly to you right now. I'll be back here when I have enough information to do so.

Re: Issue with some temporary tables being created

dickelsa — Tue, 14 Oct 2014 13:10:15 GMT

Can you maybe attach a screenshot of tableview? then? i think the problem is that you are loading a table underneath it, from the tabel, but not renaming it. this causes a new 'fragmented' table with

Re: Re: Issue with some temporary tables being created

martinpohl — Tue, 14 Oct 2014 13:15:34 GMT

see attached script.

it counts the number of tables in your data model an delete them one by one.

For example 3 load 3 tables, delet one table in script and than delete the other two automatical.

Regards

Re: Issue with some temporary tables being created

datanibbler — Tue, 14 Oct 2014 13:17:35 GMT

Hi dick,

that sounds quite good. Seems you understand the inner workings quite well.

Do I have to rename the table? I have only one line to process, with four records in it, so that "FROM_FIELD LOAD" should generate only one table with four records?`

(I have now found out that the underlying reason is that my 1st subroutine runs only once on that list, not three times like it should - thus, two unusable records remain in that table and are processed in the 2nd subroutine which should actually not be the case).

The table_fragment created is named "SOFA_Liste_v2-1", so I guess it is created in these lines:

Noconcatenate

SOFA_Liste_v2:
LOAD
*
From_Field(Temp1, Mat_unsauber)
(no labels);
RENAME FIELD @1 TO Sachnr_und_Anz_$(v_line2);

Thanks a lot!

Re: Issue with some temporary tables being created

datanibbler — Tue, 14 Oct 2014 13:38:09 GMT

Woah!

I didn't actually do anything specific - well, I'm sure I did do something, but not along a specific line of action - but now it seems to work.

Let's see.

Thank you all for your help!

Re: Issue with some temporary tables being created

datanibbler — Wed, 15 Oct 2014 06:23:26 GMT

Hi Martin,

I used your code in my app and now it all runs fine. Thanks a lot for that!

I would like to make it part of our internal "development guidelines" - to make scripts "clean up their act". That's very important in loops as I have experienced - but a sensible thing to do in all situations (I already have a way of removing used variables and removing unnecessary qvd_files).

I haven't yet found out how to modify the code so that one table is left alone and all the others are deleted - for when you need to do an interim_cleaning in the process.

Can you help me there?

Thanks a lot!

Best regards,

DataNibbler

Re: Re: Issue with some temporary tables being created

martinpohl — Wed, 15 Oct 2014 07:04:21 GMT

Hello DataNibbler,

I modified the script so now you can declare a table which not ne deleted.

in Line 57 in the script you can define it. You can also combine this if statement with more than one table or the number of lines in the table and so on.

Regards

Re: Issue with some temporary tables being created

datanibbler — Wed, 15 Oct 2014 07:25:43 GMT

-Thanks a lot!

You did it the other way round. That is probably the difference.

I tried to just delete all the tables where the specified tablename (the one to keep) is not encountered. I didn't think of just exiting. I guess I could modify that so that only the loop is exited, not the entire script.

Best regards,

DataNibbler

P.S.: You have TableName(0) there and TableName(1) - would you mind telling me what is the logic behind that?