topic Re: Problem with IF statement in an Expression in QlikView

Problem with IF statement in an Expression

amber2000 — Thu, 12 Dec 2013 11:20:35 GMT

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

Re: Problem with IF statement in an Expression

manideep78 — Thu, 12 Dec 2013 11:40:18 GMT

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.

Re: Problem with IF statement in an Expression

amber2000 — Thu, 12 Dec 2013 12:57:43 GMT

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";

Re: Problem with IF statement in an Expression

Clever_Anjos — Thu, 12 Dec 2013 13:09:25 GMT

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

Re: Problem with IF statement in an Expression

manideep78 — Thu, 12 Dec 2013 13:14:45 GMT

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]

Re: Problem with IF statement in an Expression

amber2000 — Thu, 12 Dec 2013 13:34:55 GMT

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

Re: Problem with IF statement in an Expression

manideep78 — Thu, 12 Dec 2013 13:47:38 GMT

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.

Re: Problem with IF statement in an Expression

Colin-Albert — Thu, 12 Dec 2013 13:51:22 GMT

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";

Re: Problem with IF statement in an Expression

manideep78 — Thu, 12 Dec 2013 13:53:29 GMT

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]