Why there is no FinalTable created?

[nenadvukovic]

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);

next

drop field Key;

TableFinal:

load * resident tmpTable

where Amt1 <> 0 and Amt2 <> 0;

drop table tmpTable;

[marcus_malinow]

Change your TableFinal LOAD to a NOCONCATENATE LOAD

[#3]

take this

TableFinal:

noconcatenate

load * resident tmpTable

where Amt1 <> 0 and Amt2 <> 0;

drop table tmpTable;

[marcus_malinow]

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.

[giakoum]

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

[fosuzuki · Accepted solution]

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;

next

drop field Key;

TableFinal:

NoConcatenate

load *

resident Table

where Amt1 <> 0 and Amt2 <> 0;

drop table Table;

[nenadvukovic]

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

[fosuzuki]

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.