topic Re: If statement not working in load script in QlikView

If statement not working in load script

simospa — Sat, 30 Aug 2014 10:36:56 GMT

I have a script that build some fileds calculating in order to different contition.

I try to do this:

LOAD x,y,z,

sum(IF (Month='8' and Year=year(Today(0)), IF(Month<Month(Today()), IF(TabName = 'CX', CX_Liters,0) , IF (TabName = 'B1', B1_Liters , IF (TabName = 'B0' , B0_Liters , IF (TabName = 'B4' , B4_Liters , IF (TabName = 'B3', B3_Liters ) ) )

)),0)) as Actual_Agoust

...

FROM myqvdfile.qvd

GROUP BY x,y,z

I want this behavior: if the month value is August and year is 2004, then if month value is minor than the actual (in this case August) I want check tableName="CX" and sum CX Liters. Otherwise, if month is not minus of actual month, I want to test Table Name: If B1, then sum B1_Liters, ELSE if B4 then sum B4 Liters, ELSE if B3 then sum B3_Liters in this order.

My big problem is that the script provide to sum either B1, AND B3, AND B3... while I want that if it finds B1 value it sum only B1_Liters and not others (B4, B3).

Can anyone help me please?

Simone

Re: If statement not working in load script

mrooney — Sat, 30 Aug 2014 15:39:18 GMT

Hi Simone,

not sure if I understand the problem quite right.

I think that you want this:

sum(IF (Month='8' and Year=year(Today(0)),

IF(Month<Month(Today()), // Here because Mont = 8 and Year = actual year

IF(TabName = 'CX', CX_Liters,0) , // here because TableName = 'CX'

IF (TabName = 'B1', B1_Liters , // Here because Table Name = 'B1'

IF (TabName = 'B0' , B0_Liters , // Here because TableName = 'B0'

IF (TabName = 'B4' , B4_Liters , // Here because TableName = 'B4'

IF (TabName = 'B3', B3_Liters) // Here because TableName = 'B3'

)

) as Actual_Agoust.

Hope helps

Re: If statement not working in load script

simospa — Sat, 30 Aug 2014 16:29:53 GMT

Thansk Miguel, but it doesn't works... the behaviuor is the same in my original statement.

I want that, if I find B1, it doesn's sum B0, B4, B3 too...

Re: If statement not working in load script

mrooney — Sat, 30 Aug 2014 17:57:17 GMT

Please send me a test example. It seems to me the expression should work..

Re: If statement not working in load script

simospa — Sat, 30 Aug 2014 18:09:06 GMT

I'm agree, technically it works as work mine... pheraphs the problem is instead in graph expression, where I use set analisys.

I have

sum( {$<year= {$(actual_year)}, Mese = {8}>} actual_agoust)

but I get all the results for August. But I want that, in this order, if sum for tablename=''B1" is zero then get sum for tablename='"B0", if this is zero then get the sum for tablename="B4" and if zero then get sum for tablename="B3".

In this order... exists a way to manage this behaviour with set analysis?

Thanks

Re: If statement not working in load script

mrooney — Sat, 30 Aug 2014 18:23:10 GMT

Well,

I think that if you want to add amount from different tables, the you'll need to define different accumulaion fields for each one in your script.

After that, in the chart you can manage this situation with set analysis.

sum( {$<year= {$(actual_year)}, Mese = {8}>} (If B1Acumm >0, B1Acum, If....., if....))

Re: If statement not working in load script

simospa — Sat, 30 Aug 2014 19:19:05 GMT

Nice idea, so I can store value in different columns and use it as you suggest...

I'll try...

Thanks, I tell you asap.