topic Re: automatic load concatenation with while in QlikView

automatic load concatenation with while

herbert_beck — Thu, 17 Jul 2014 11:36:29 GMT

Dear all,

automatic concatenation does not always work with WHILE:

class:
LOAD IterNo() AS class
WHILE IterNo() <= 5
;
LOAD 6 AS class
AutoGenerate (1)
;
LOAD 7 AS class
AutoGenerate (1)
;

The second Load-statement is not executed, class 6 is not loaded.

Replace WHILE IterNo() <= 5 with AutoGenerate(5) and replace IterNo() with RowNo() and the script will work fine.

Kind regards,

Herbert

Re: automatic load concatenation with while

hic — Thu, 17 Jul 2014 12:10:55 GMT

It works fine.

In your script you have two tables concatenated into one: the first Load is a preceding Load that loads from the second load. But the field "class" from the second load is not carried through since this field isn't listed in the first load. But the resulting table nevertheless has 6 rows - five from the first two loads, and 1 from the last.

Try this instead:

class:
LOAD class & '-' & IterNo() AS class
     WHILE IterNo() <= 5 ;
LOAD 6 AS class
     AutoGenerate (1);
LOAD 7 AS class
     AutoGenerate (1);

HIC

Re: automatic load concatenation with while

tresB — Thu, 17 Jul 2014 12:11:32 GMT

Returns an integer for the number of the currently read row of the source data table.

In your first load, there is nothing (a record) to load at the very first read, hence iterno() returns nothing. So your first load :

LOAD IterNo() AS class
WHILE IterNo() <= 5
;
is loading nothing.

The rest behavior is not yet clear to me. I will get back if can comprehend something meaningful.

Re: automatic load concatenation with while

Thu, 17 Jul 2014 12:31:48 GMT

HI Herbert, You have only 2 Load statements and so you got 6 rows.

Try like below:

class:
LOAD IterNo() AS class
AutoGenerate (1) WHILE IterNo() <= 5
;
LOAD 6 AS class
AutoGenerate (1)
;
LOAD 7 AS class
AutoGenerate (1)
;

The above example have the 3 load statement having 3 sources.

Re: automatic load concatenation with while

herbert_beck — Thu, 17 Jul 2014 13:07:05 GMT

Thanks for your responses:)) But now I am more confused.

class:
LOAD IterNo() AS class
WHILE IterNo() <= 5
;

loads nothing :((

class:
LOAD IterNo() AS class
WHILE IterNo() <= 5
;
LOAD 6 AS class
AutoGenerate (1)
;

loads 5 rows (:(

class:
LOAD IterNo() AS class
WHILE IterNo() <= 5
;
LOAD 6 AS class
AutoGenerate (1)
;
LOAD 7 AS class
AutoGenerate (1)
;

is loading 6 rows: 1,2,3,4,5 and 7 ???

class:
LOAD RowNo() AS class
AutoGenerate(5)
;
LOAD 6 AS class
AutoGenerate (1)
;
LOAD 7 AS class
AutoGenerate (1)
;

is loading all 7 rows. This is the only thing that looks fine. Therefore I will apply script no 4).

Kind regards,

Herbert

Re: Re: automatic load concatenation with while

hic — Thu, 17 Jul 2014 13:18:49 GMT

You should read Preceding Load.

A preceding load needs a following Load statement. Together they form one table.

class:

LOAD IterNo() AS class

WHILE IterNo() <= 5

;

LOAD 6 AS class

AutoGenerate (1)

;

LOAD 7 AS class

AutoGenerate (1)

;

The red part forms one table and the blue part forms a second.

HIC

Re: automatic load concatenation with while

tresB — Fri, 18 Jul 2014 05:59:46 GMT

Let me explain my understanding:

The bottommost load statement is generating one record '7' (field- class). The middle one is producing record '6' and being referenced in the uppermost load (prceding load) and it iterates over the same record ('6' - but note the value '6' itself is not referred in the preceding load, it is actually being used as a record for iteration) five times and generating 1,2,3,4,5 by the iterno(). Now the two tables (the bottommost one and uppermost one) are getting concatenated to get 1,2,3,4,5,7. Don't get surprised that '6' is not there, because the middle table is not existing separately to be concatenated it is just been used as the input for uppermost table.

To answer the question -why the first load alone can't generate a table - There is no record to be iterated on in that load, hence it can't generate a table. To be able to use the similar load you have to use Autogenerate() alongwith it or some table already existing.

Re: automatic load concatenation with while

herbert_beck — Tue, 22 Jul 2014 16:56:31 GMT

Thank you very much for your explanations. I was not familiar with the concept of a preceeding LOAD, which has no AUTOGENERATE, FROM or RESIDENT. Your explanations are absolutely plausible.

Kind regards,

Herbert