topic Re: Create status field in load based on four separate fields in QlikView

Create status field in load based on four separate fields

Fri, 24 Oct 2014 20:46:02 GMT

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.

Re: Create status field in load based on four separate fields

MarcoWedel — Fri, 24 Oct 2014 20:55:03 GMT

LOAD somefields,

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

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

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

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

FROM YourSource;

Re: Create status field in load based on four separate fields

markodonovan — Fri, 24 Oct 2014 20:57:39 GMT

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

Re: Create status field in load based on four separate fields

Fri, 24 Oct 2014 21:01:22 GMT

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.

Re: Create status field in load based on four separate fields

maxgro — Fri, 24 Oct 2014 21:04:11 GMT

load

somefields,

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

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

from

somesource;