Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
nenadvukovic
Creator III
Creator 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
Partner - Specialist III
Partner - Specialist III

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;

View solution in original post

7 Replies
marcus_malinow
Partner - Specialist III
Partner - Specialist III

Change your TableFinal LOAD to a NOCONCATENATE LOAD

Not applicable

take this

TableFinal:

noconcatenate

load * resident tmpTable

where Amt1 <> 0 and Amt2 <> 0;

drop table tmpTable;

marcus_malinow
Partner - Specialist III
Partner - Specialist III

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
Partner - Master II
Partner - Master II

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

fosuzuki
Partner - Specialist III
Partner - Specialist III

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
Creator III
Creator III
Author

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

fosuzuki
Partner - Specialist III
Partner - Specialist III

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.