Script: multiple statements in if during load

[clausbarlose]

Please help - I haven't been able to find an answer on how to avoid all those ugly if's during load, and seek expert help here!

Depending on the contents of a column I wan't to assign new values to several other columns or do some counting, etc.

Take this example:

Load

    CUSTID    as CustomerID,

    if CUSTID = 'Carlsberg' then

        'Beer' as MainProduct,

       'More beer' as SecoundaryProduct

        0 as HP

    else

        'Other' as MainProduct,

        'Something else' as SecoundaryProduct

        1 as HP

    end if

...

Kind regards,

Claus

[petter · Accepted solution]

LOAD

   CUSTID as CustomerID,

   If( CUSTID = 'Carlsberg' , 'Beer' , 'Other' ) AS MainProduct,

   If( CUSTID = 'Carlsberg' , 'More beer', 'Something else' ) AS SecondaryProduct,

   If( CUSTID = 'Carlsberg' , 0 , 1 ) AS HP,

   .....

[robert_mika]

Look at  this topic

Don't join - use Applymap instead

[rubenmarin]

Hi Claus, another option is using ApplyMap():

Map_MainProduct:

Mapping LOAD * Inline [

    Cust, Prod

    Carlsberg, Beer

]; //This can be loaded from the table with the relations, i used inline as an example

// Same for Map_SecondaryProduct and Map_HP

//then the real load

table:

LOAD

   CUSTID as CustomerID,

   ApplyMap('Map_MainProduct', CUSTID, 'Other' ) AS MainProduct,

   ApplyMap('Map_SecondaryProduct', CUSTID, 'Something else' ) AS SecondaryProduct,

   ApplyMap('Map_HP', CUSTID, 1 ) AS HP,

...

[clausbarlose]

Hi Petter

Thanks, but this is what I wish to avoid. Those ugly multiple if's..

[clausbarlose]

Hi Ruben

Thanks, I'll try this out. Had hopes that Pascal's Case function was available...