topic Re: Error in IF them Else Expression in QlikView

Error in IF them Else Expression

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Error in IF them Else Expression

lironbaram — Tue, 22 Nov 2016 12:18:54 GMT

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')

));

Re: Error in IF them Else Expression

sunny_talwar — Tue, 22 Nov 2016 12:24:40 GMT

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')

));

Re: Error in IF them Else Expression

ramasaisaksoft — Tue, 22 Nov 2016 12:29:44 GMT

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?

Re: Error in IF them Else Expression

Peter_Cammaert — Tue, 22 Nov 2016 12:30:42 GMT

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

Re: Error in IF them Else Expression

Tue, 22 Nov 2016 12:34:57 GMT

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,

Re: Error in IF them Else Expression

Peter_Cammaert — Tue, 22 Nov 2016 12:43:46 GMT

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)

Re: Error in IF them Else Expression

Tue, 22 Nov 2016 12:51:23 GMT

Sorry Peter, I didn´t understand.

Could you please give me a sample?

Re: Error in IF them Else Expression

Tue, 22 Nov 2016 13:34:42 GMT

Thank you for everyone that answered my question.

Regards@