topic Re: Load Exit - Conditional in QlikView

Load Exit - Conditional

tresB — Tue, 07 May 2013 05:38:25 GMT

Hello,

I want my load script to stop once a certain value is reached for a field. Something like :

Load A,

From .... exit/stop load(should not exit script) when found B='XX'(line number is not fixed, so can't use recno());

Thanks in advance.

Re: Load Exit - Conditional

er_mohit — Tue, 07 May 2013 05:42:20 GMT

Test:

load A,

from Test where B='XX';

hope it helps

Re: Load Exit - Conditional

tresB — Tue, 07 May 2013 05:52:21 GMT

Hello Mohit,

Thanks for your reply. However, this is not what i want. i want my LOAD to STOP reaching at the condition.

Thanks.

Re: Load Exit - Conditional

Tue, 07 May 2013 05:54:12 GMT

exit script when B='XX'

Re: Load Exit - Conditional

tresB — Tue, 07 May 2013 05:58:30 GMT

Exit LOAD is desired, not exit SCRIPT.

Thanks.

Re: Load Exit - Conditional

Tue, 07 May 2013 06:01:48 GMT

Test:

load A,

from table;

exit script when B='XX'

test2:

load * from table2;

note--

here in Test table, if B='XX' found, then exit or stop the script, so test2 table not run.

Re: Load Exit - Conditional

tresB — Tue, 07 May 2013 06:20:24 GMT

See, the scenario is - my source file has multiple tables. first table ends by indicating that field B="XX" for first table and then the second table starts. Therefore i have to stop load for first table at a condition and start the second table load. that means i can't use 'EXIT SCRIPT'. by using multiple stages (storing to qvd and then using flag or variable), may be i can achieve the same. But i was just wondering if there is any easier way(without storing in qvd).

Thanks.

Re: Load Exit - Conditional

rlp — Tue, 07 May 2013 07:53:03 GMT

You can use the exists() function which indicates if a certain value exists in a specified field.

This gives the following script:

<your_table> :

NoConcatenate

LOAD

A ,

FROM <your_source>

WHERE NOT Exists( B , 'XX' ) ;

Once done, you have the value 'XX' in your field B. In order to eliminate it, you should do:

tmp:

RIGHT KEEP( <your_table> )

LOAD

RESIDENT <your_table>

WHERE B <> 'XX' ;

DROP TABLE tmp ;

Re: Load Exit - Conditional

tresB — Tue, 07 May 2013 08:11:54 GMT

Hello Richard,

Thanks for your input. if exists() is used, the load goes on even after reaching the conditional value(i.e. the followed records would not be excluded in load). i want the load to suspend the further records load once the conditon is reached. something logical like use of BREAK and jump to another load statement in the script.

Thanks.

Re: Load Exit - Conditional

rlp — Tue, 07 May 2013 09:20:08 GMT

It's strange that the 'NOT exists()' doesn't work.

You can try a more manual method, which consists of creating another field which contains '0' until 'XX' occured and '1' after:

In your load, add this definition:

<your_table> :

NoConcatenate

LOAD

...

if( peek(indic_occured_value) OR B='XX' , '1' , '0' ) as indic_occured_value

FROM <your_source> ;

tmp:

RIGHT KEEP( <your_table> )

LOAD

'0' as indic_occured_value

AUTOGENERATE( 1 ) ;

DROP TABLE tmp ;

If you want to keep, the first occurence of 'XX' , you should replace B='XX' by peek(B) = 'XX' in the expression of indic_occured_value ;

Re: Load Exit - Conditional

Gysbert_Wassenaar — Tue, 07 May 2013 09:35:45 GMT

I don't think so. I'm afraid you'll need something like this:

T1:

load B, if(B='XX',recno()) as flag

from ...somewhere...;

T2:

load min(flag) as firstmatch

resident T1;

Let vFirst = peek('firstmatch');

drop table T1, T2;

T3:

first $(vFirst)

load * from ...somewhere...;

Re: Load Exit - Conditional

tresB — Tue, 07 May 2013 09:52:36 GMT

Yes Gysbert, as i mentioned earlier - "using multiple stages (storing to qvd and then using flag or variable), may be i can achieve the same" - seems that, this is the way(like you suggested) i have to follow. Probably there is no direct way/function/command to break out from the LOAD process.

Anyway thank you all.