topic Re: Temporary tables not being dropped in QlikView

Temporary tables not being dropped

datanibbler — Mon, 05 Jan 2015 14:46:39 GMT

Hi,

I have a very strange problem: There is one Excel_file from which I load data in several steps:

- There are 4 types of costs in that sheet, so I load 4 tables, one for each cost_type.

- Then one total_table is generated

- I concatenate to that RESIDENT from each of those 4 tables (one by one)

- That done, I drop the temporary table

What happens is this:

The 4 temporary tables are created, the total_table is created, it is saved and dropped just as it should

<=> The 4 temporary tables are not dropped though it is in the script, so of course I am getting synthetic keys.

Can anybody help me with that?

I can attach my code no problem. Sample data will be more complicated. I will try to work it out in the debugging_mode. Maybe someone can give me a hint, though.

Thanks a lot!

Best regards,

DataNibbler

Re: Temporary tables not being dropped

datanibbler — Mon, 05 Jan 2015 14:52:01 GMT

Hi,

in the Debugging_mode what happens is this:

- The first two LOADs (from the same Excel_file) are fine.

- Then comes the first of those 4 temp_tables (from a second file)

=> Somehow, the Debugger stops at the NOCONCATENATE keyword and LOADs - but does not forware the "cursor", so the LOAD is done twice!?

Indeed, these 4 "second tables" - with the "-1" as a suffix - are the ones which are not dropped and which form the synthetic keys.

Re: Temporary tables not being dropped

Mon, 05 Jan 2015 14:55:30 GMT

Hi,

try to concatenate explicitly to the Fact Table and load work tables with NoConcatenate

Like FE:

Worktable1: noconcatenate

Load * from Table2;

FactTable: noconcatenate

load * from Table1;

concatenate(FactTable)

load * resident WorkTable1;

drop table WorkTable1

etc...

HTH

Re: Temporary tables not being dropped

datanibbler — Mon, 05 Jan 2015 15:01:28 GMT

Hi,

thanks for the answer!

I have just found out what is the reason for my woes: It is the NOCONCATENATE keyword - there are 2 Excel_files in that directory, 1 for 2014 and 1 for 2015. That is fine so far. I load them both with the * - but since I have that NOCONCATENATE, the 2 are loaded as 2 separate tables ....

I have to find a way to concatenate those 2 to each other, but not to the tables I loaded before ...

Can you think of a way to do this?

I had similar scenarios before and in one instance what I did was store and drop one table and load it again afterwards, but that is not as straightforward as I'd like to do this ...

Re: Temporary tables not being dropped

Mon, 05 Jan 2015 15:08:32 GMT

I'm not sure if this will solve your issue, but if you are looking to concatenate both years into a single table for each load, you could try to do a loop, you just need to create a blank table to load onto before each loop. Basically just an inline table to concatenate onto and then a directory load loop using your different load requirements.

Re: Temporary tables not being dropped

rubenmarin — Mon, 05 Jan 2015 15:10:25 GMT

Hi DataNibble, I used two ways to make that, one is creating an empty table and always concatenate to that table, ie:

Ladegeräte_Stapler:

LOAD * Inline [dumbfield];

Then when you read from Excel you can use Concatenate(Ladegeräte_Stapler) instead of NoConcatenate.

The other way is to use a variable to use NoConcatenate or Concatenate:

LET vConcatenate = 'NoConcatenate'

FOR EACH file in...

Ladegeräte_Stapler:

$(vConcatenate)

LOAD ....

FROM Excel;

LET vConcatenate = 'Concatenate (Ladegeräte_Stapler)'