topic Re: Why there is no FinalTable created? in QlikView

Why there is no FinalTable created?

nenadvukovic — Fri, 08 Aug 2014 14:03:29 GMT

Hi, this sounds like a trivial case but it drives me crazy today. I am using this way in tens of apps. Maybe it is not a good day to work with QV

I end up with a table called "tmpTable-1" and with no "Amt2" field.

What is wrong with this script? Of course, it a test script but the principle is the same. Thanks

for I = 1 to 2

vCo = if(I = 1, 'C1', 'C2');

tmpTable:

load

'$(vCo)' as Co,

'$(vCo)_'&ID as Key,

ID,

Amt1

from test.xlsx (ooxml, embedded labels, table is Sheet1);

left join (tmpTable) load

'$(vCo)_'&ID as Key,

Amt2

from test.xlsx (ooxml, embedded labels, table is Sheet2);

drop field Key;

TableFinal:

load * resident tmpTable

where Amt1 <> 0 and Amt2 <> 0;

drop table tmpTable;

Re: Why there is no FinalTable created?

marcus_malinow — Fri, 08 Aug 2014 14:05:13 GMT

Change your TableFinal LOAD to a NOCONCATENATE LOAD

Re: Why there is no FinalTable created?

Fri, 08 Aug 2014 14:05:59 GMT

take this

TableFinal:

noconcatenate

load * resident tmpTable

where Amt1 <> 0 and Amt2 <> 0;

drop table tmpTable;

Re: Why there is no FinalTable created?

marcus_malinow — Fri, 08 Aug 2014 14:06:36 GMT

The reason there is no TableFinal is that QlikView is doing an auto concatenate as the field list for this table is the same as that for your previously loaded table.

Re: Why there is no FinalTable created?

giakoum — Fri, 08 Aug 2014 14:17:53 GMT

One other solution would be to create the table outside the for loop (with autogenerate(0) fr example) and concatenate in the loop

Re: Why there is no FinalTable created?

fosuzuki — Fri, 08 Aug 2014 14:54:43 GMT

Hi Nenad,

there are other ways of doing it, but one of them is:

for I = 1 to 2

vCo = if(I = 1, 'C1', 'C2');

tmpTable:

load

'$(vCo)' as Co,

'$(vCo)_'&ID as Key,

ID,

Amt1

from test.xlsx (ooxml, embedded labels, table is Sheet1);

left join (tmpTable) load

'$(vCo)_'&ID as Key,

Amt2

from test.xlsx (ooxml, embedded labels, table is Sheet2);

If $(I) = 1 then

Table:

NoConcatenate

LOAD *

Resident tmpTable;

ELSE

Concatenate(Table)

LOAD *

Resident tmpTable;

ENDIF

DROP Table tmpTable;

drop field Key;

TableFinal:

NoConcatenate

load *

resident Table

where Amt1 <> 0 and Amt2 <> 0;

drop table Table;

Re: Why there is no FinalTable created?

nenadvukovic — Fri, 08 Aug 2014 15:09:14 GMT

Thank you Fernando. I do have scripts where this works fine without this if..endif part.

Re: Why there is no FinalTable created?

fosuzuki — Fri, 08 Aug 2014 16:17:05 GMT

I'd guess that the scripts that work for you do not have a JOIN inside the loop.

The JOIN breaks the autoconcatenate, and then multiple "[tablename]-N" tables are created.