SQL If Statement

[#1]

Hi,

I am trying to recreate the following SQL if statement in Qlikview:

if {F0902.GBLT} = "AA"

and ({F0902.GBFY}) = ({?Year})

and (({F0902.GBOBJ} in '27000' to '27999')

or ({F0902.GBOBJ} in '31000' to '31999')

or ({F0902.GBOBJ} in '42000' to '42399'))

then

{F0902.GBAPYC}

I have the set Analysis correct however I am having trouble wording the Qlikview if statement so that it Sums GBAPYC if 27000 object range has a value or sums the 27000 + 31000 range if the 31000 has a value and ignores 42000 range or it sums 27000 + 42000 if the 42000 has a value and ignores the 31000 range.

What I have so far is:

if

(sum({<PeriodwithAPYC={'$(=getfieldselections(PeriodwithAPYC))', 'BegBal'}, [Ledger Type]={'AA'}, [Fiscal Year]={'$(=getfieldselections([Fiscal Year])-1)'}, [Object Account]={'>=31000 <=31999'}>}Amount)<>0,

$(vActualYTDLY31000),

if(sum({<PeriodwithAPYC={'$(=getfieldselections(PeriodwithAPYC))', 'BegBal'}, [Ledger Type]={'AA'}, [Fiscal Year]={'$(=getfieldselections([Fiscal Year])-1)'}, [Object Account]={'>=42000 <=43299'}>}Amount)<>0,

$(=vActualYTDLY42000), 0))

The variables do the following:

$(vActualYTDLY31000) = Sums 27000 range and the 31000 range

$(=vActualYTDLY42000) - Sums 27000 raneg and the 42000 range

So my if statment is saying if the 31000 range is not equal to 0 (as it contains negative & positive amounts) then Sum 27000 range + 31000 Range or if the 42000 range is not equal to 0 sum 27000 range + 42000 range.

Please advise if you can help me with this one?

Thanks,

Nikki Fairgrieve

[Anonymous]

Usually if statements in expressions take the format sum(if(.... instead of if(sum(...

This is because you normally want to test the value at the field level, instead of aggregating a field before then testing.

If you are trying to replicate an SQL statement it too may need to be done at the field level instead of at an aggregated level.

Jonathan