Error in IF them Else Expression

[#1]

Dear all,

I need to create a IF then else, but I got an error expression.

Could you please help me?

SGA_OCST:

LOAD

     [C. Lucro],

    [Moeda int.]/-1000 as [Valor/moeda objeto],

    if mid([C. Lucro],6,2)= '1A' or mid([C. Lucro],6,2) = '1C' then

    'SG&A' as [P&L DSC];

   ELSEIF mid([C. Lucro],6,2) = '1O' then

        'OTHER COSTS' as [P&L DSC];

      ENDIF

FROM

ECCS_SGA_OCST.xlsx

(ooxml, embedded labels, table is Sheet1, filters(

ColXtr(1, RowCnd(CellValue, 1, StrCnd(null)), 0),

Top(12, 'Actual'),

Replace(12, top, StrCnd(null)),

Top(12, 'Cenario')

));

What is wrong in the expression that FROM is not blue?

Regards

[lironbaram · Accepted solution]

the syntax in qlik is a little bit different

SGA_OCST:

LOAD

     [C. Lucro],

    [Moeda int.]/-1000 as [Valor/moeda objeto],

    if (mid([C. Lucro],6,2)= '1A' or mid([C. Lucro],6,2) = '1C' , 'SG&A' ,

        IF (mid([C. Lucro],6,2) = '1O',        'OTHER COSTS' )) as [P&L DSC]

FROM

ECCS_SGA_OCST.xlsx

(ooxml, embedded labels, table is Sheet1, filters(

ColXtr(1, RowCnd(CellValue, 1, StrCnd(null)), 0),

Top(12, 'Actual'),

Replace(12, top, StrCnd(null)),

Top(12, 'Cenario')

));

[sunny_talwar]

Or this:

SGA_OCST:

LOAD [C. Lucro],

    [Moeda int.]/-1000 as [Valor/moeda objeto],

   Pick(Match(Mid([C.Lucro], 6, 2), '1A', '1C') + 1, 'OTHER COSTS', 'SG&A') as [P&L DSC]

FROM ECCS_SGA_OCST.xlsx

(ooxml, embedded labels, table is Sheet1, filters(

ColXtr(1, RowCnd(CellValue, 1, StrCnd(null)), 0),

Top(12, 'Actual'),

Replace(12, top, StrCnd(null)),

Top(12, 'Cenario')

));

[ramasaisaksoft]

Hi Suzel,

in Qlikview If(a>b,a,b)

a>b is a condition

a-> condition success then a

b-> condition failure then b

SGA_OCST:

LOAD

     [C. Lucro],

    [Moeda int.]/-1000 as [Valor/moeda objeto],

    if( mid([C. Lucro],6,2)= '1A' or mid([C. Lucro],6,2) = '1C' ,'SG&A',

     IF( mid([C. Lucro],6,2) = '1O','OTHERCOSTS')

     )as [P&L DSC]

FROM

ECCS_SGA_OCST.xlsx

(ooxml, embedded labels, table is Sheet1, filters(

ColXtr(1, RowCnd(CellValue, 1, StrCnd(null)), 0),

Top(12, 'Actual'),

Replace(12, top, StrCnd(null)),

Top(12, 'Cenario')

));

so check this expression .and let us know the update.weather ur issue is solved or not?

[Peter_Cammaert]

In QlikView Script, there are two IF constructs and you'll have to use them in the proper place or they will throw an error.

• IF THEN ELSE END IF is a control statement that can only be used at statement level (e.g. not inside statements like LOAD). The statement will not produce results but may change the statement sequence
• IF() function is a real QlikView function that can be used in every regular QlikView expression (pretty powerful). This one can be used inside a LOAD statement to produce a value for a new column definition, just like Liron demonstrated before. You can use IF() in load scripts, but also in every UI expression field (with a grey button with three dots to the right).

See the QlikView Desktop Help for more information, differences and examples.

Peter

[#6]

Hi Liron,

But what I need is include more action in the IF clauses

  [C. Lucro],

    [Moeda int.]/-1000 as [Valor/moeda objeto],

    if mid([C. Lucro],6,2)= '1A' or mid([C. Lucro],6,2) = '1C' then

    'SG&A' as [P&L DSC];

'SG&A' as [FAF DSC];
'9700' as [P&L COD];
'9700' as [FAF COD];

   ELSEIF mid([C. Lucro],6,2) = '1O' then

        'OTHER COSTS' as [P&L DSC];

'OTHER COSTS' as [P&L DSC];
'OTHER COSTS' as [FAF DSC];
'9600' as [P&L COD];
'9600' as [FAF COD];

      ENDIF

FROM

ECCS_SGA_OCST.xlsx

(ooxml, embedded labels, table is Sheet1, filters(

ColXtr(1, RowCnd(CellValue, 1, StrCnd(null)), 0),

Top(12, 'Actual'),

Replace(12, top, StrCnd(null)),

Top(12, 'Cenario')

));

How should I fiexed this?

regards,

[Peter_Cammaert]

This IF you are using needs to be a function, which means it needs parentheses to contain the parameter list (either 2 or 3 parameters are allowed).

You cannot embed an IF THEN ELSE in another statement (like you can do with a CASE in SQL SELECT)

[#8]

Sorry Peter, I didn´t understand.

Could you please give me a sample?

[#9]

Thank you for everyone that answered my question.

Regards@