Problem with IF statement in an Expression

[amber2000]

Hello everyone,

Can someone please help me with the following Expression?

More specific with the If statement.

The original expression is:

sum({$<[Jobcode Flag] = {"Productive"}>} [Assignment Total Colli])) 

This workes fine but I want to include an if.

If([Multi Picking Description] = ' ', 'REG',[Multi Picking Description])

If  [Multi Picking Description] is an empty string, place 'REG' ,else [Multi Picking Description]

I need to do this because there are empty fields in this table that I want to replace with 'REG' so I can have REG in my chart instead of -

I've tried so many ways but can't get it to work without error.

sum({$<[Jobcode Flag] = {"Productive"}>} If([Multi Picking Description] = '', 'REG',[Multi Picking Description],[Assignment Total Colli])) 

Monique

[manideep78]

Hi those two are entirely different expressions. why do you want to merge those expressions? you can add IF in script level or in an another expression. What exactly do you want. I don't think I understood this.

[amber2000]

The following expression gives the results for the dimension Picking Method you see in the chart.

sum({$<[Jobcode Flag] = {"Productive"}>} [Assignment Total Colli])) 

The green bar has no value in front and is represented as - because the value is empty.

I would like a workaround to replace the - with Regular

This is the script, how can I integrate If([Multi Picking Code] = '' , 'REG', [Multi Picking Code]) in the load below?

Load [SK_Multi_Picking] as [Multi Picking ID],

     [BK_Multi_Picking_Cd] as [Multi Picking Code],

     [Picking_Method_Desc] as [Multi Picking Description]

where Exists ([Multi Picking ID],[SK_Multi_Picking]);

SQL SELECT [SK_Multi_Picking],

    [BK_Multi_Picking_Cd],

    [Picking_Method_Desc]

FROM DWH.dbo."D_Multi_Picking";

[Clever_Anjos]

I think you should use If([Multi Picking Description] = ' ', 'REG',[Multi Picking Description]) as dimension, not inside your expression

[manideep78]

Use preceding load

Load *, If([BK_Multi_Picking_Cd] ='','REG',[BK_Multi_Picking_Cd] ) as [Multi Picking Code];

Load [SK_Multi_Picking] as [Multi Picking ID],

     [BK_Multi_Picking_Cd] as [Multi Picking Code1],

     [Picking_Method_Desc] as [Multi Picking Description]

where Exists ([Multi Picking ID],[SK_Multi_Picking]);

SQL SELECT [SK_Multi_Picking],

    [BK_Multi_Picking_Cd],

    [Picking_Method_Desc]

FROM DWH.dbo."D_Multi_Picking";

drop field [Multi Picking Code1]

[amber2000]

The preceding load generates errors when I run it

Field not found - <BK_Multi_Picking_Cd>

SQL SELECT [SK_Multi_Picking],

        [BK_Multi_Picking_Cd],

           [Picking_Method_Desc]

FROM DWH.dbo."D_Multi_Picking"

Field not found

Did not find the field "Multi Picking Code1" from the DROP FIELD statement

[manideep78]

Load *, If([Multi Picking Code1] ='','REG',[Multi Picking Code1] ) as [Multi Picking Code];

Load [SK_Multi_Picking] as [Multi Picking ID],

     [BK_Multi_Picking_Cd] as [Multi Picking Code1],

     [Picking_Method_Desc] as [Multi Picking Description]

where Exists ([Multi Picking ID],[SK_Multi_Picking]);

SQL SELECT [SK_Multi_Picking],

    [BK_Multi_Picking_Cd],

    [Picking_Method_Desc]

FROM DWH.dbo."D_Multi_Picking";

drop field [Multi Picking Code1]

you should be able to do this.

[Colin-Albert]

Just change you load script to include the if statement. There is no need to use an additional preceeding load

Load [SK_Multi_Picking] as [Multi Picking ID],
     If([BK_Multi_Picking_Cd] ='', 'REG',[ BK_Multi_Picking_Cd] ) as [Multi Picking Code],
     [Picking_Method_Desc] as [Multi Picking Description]
where Exists ([Multi Picking ID],[SK_Multi_Picking]);
SQL SELECT [SK_Multi_Picking],
    [BK_Multi_Picking_Cd],
    [Picking_Method_Desc]
FROM DWH.dbo."D_Multi_Picking";

[manideep78]

Even this should work:

Load [SK_Multi_Picking] as [Multi Picking ID],

   If( [BK_Multi_Picking_Cd]='','REG',[BK_Multi_Picking_Cd]) as [Multi Picking Code],

     [Picking_Method_Desc] as [Multi Picking Description]

where Exists ([Multi Picking ID],[SK_Multi_Picking]);

SQL SELECT [SK_Multi_Picking],

    [BK_Multi_Picking_Cd],

    [Picking_Method_Desc]

FROM DWH.dbo."D_Multi_Picking";

drop field [Multi Picking Code1]