Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

nenadvukovic
Contributor III

Why there is no FinalTable created?

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;

1 Solution

Accepted Solutions
fosuzuki
Valued Contributor II

Re: Why there is no FinalTable created?

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;

7 Replies
marcus_malinow
Valued Contributor III

Re: Why there is no FinalTable created?

Change your TableFinal LOAD to a NOCONCATENATE LOAD

Not applicable

Re: Why there is no FinalTable created?

take this

TableFinal:

noconcatenate

load * resident tmpTable

where Amt1 <> 0 and Amt2 <> 0;

drop table tmpTable;

marcus_malinow
Valued Contributor III

Re: Why there is no FinalTable created?

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
Honored Contributor II

Re: Why there is no FinalTable created?

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

fosuzuki
Valued Contributor II

Re: Why there is no FinalTable created?

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
Contributor III

Re: Why there is no FinalTable created?

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

fosuzuki
Valued Contributor II

Re: Why there is no FinalTable created?

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.

Community Browser