Load Exit - Conditional

[tresesco]

Hello,

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

Load   A,

          B

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. 

[er_mohit]

Test:

load A,

B

from Test where B='XX';

hope it helps

[tresesco]

Hello Mohit,

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

Thanks.

[#4]

exit script when B='XX'

[tresesco]

Exit LOAD is desired, not exit SCRIPT.

Thanks.

[#6]

Test:

load A,

B

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.

[tresesco]

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.

[rlp]

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 ,

     B

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

     B

RESIDENT <your_table>

WHERE B <> 'XX' ;

DROP TABLE tmp ;

[tresesco]

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.

[rlp]

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 ;