Create status field in load based on four separate fields

[#1]

I have 4 fields in a table I'm loading, the fields are called:

  PHASE1

  PHASE2

  PHASE3

  PHASE4

The fields are mutually-exclusive; one of them will always contain an 'X' while the other three will be blank.  I'd like to create one field called STATUS that would have the following values:

If PHASE1 = 'X', STATUS = 'CREATED'

If PHASE2 = 'X', STATUS = 'RELEASED'

If PHASE3 = 'X', STATUS = 'COMPLETED'

If PHASE4 = 'X', STATUS = 'CLOSED'

What would be the best way to do this in a Load statement?  I tried working with maps but couldn't figure it out since the status flags are in separate fields.

[MarcoWedel · Accepted solution]

LOAD somefields,

          If(PHASE1 = 'X', 'CREATED',

           If(PHASE2 = 'X', 'RELEASED',

            If(PHASE3 = 'X', 'COMPLETED',

             If(PHASE4 = 'X', 'CLOSED')))) as STATUS

FROM YourSource;

[markodonovan]

Hi Robert,

2 ways I can think of:

1. Nested if statements :

ie:

if(Phase1='X', 'CREATED',

   if(Phase2='X', 'RELEASED')) as STATUS

2. Multiple loads and concatenate

MainTable:

LOAD

  field1

  ,field2

  ,'CREATED' as STATUS

FROM SOMETABLE

WHERE Phase1='X';

Concatenate(MainTable)

LOAD

  field1

  ,field2

  ,'RELEASED' as STATUS

FROM SOMETABLE

WHERE Phase2='X';

The nested if statement might be easiest.

Hope this helps.

Mark

www.techstuffy.com

[#4]

Thank you both for the answers!  I should have looked into the 'If' statement more, I didn't realize I could nest them like that to act like an if/else chain.

[maxgro]

load

     somefields,

     pick(match('X', PHASE1, PHASE2, PHASE3, PHASE4),

          'CREATED','RELEASED','COMPLETED','CLOSED') as Status

from

     somesource;