IF condition takes both values

[#1]

Hello,

I have a big problem regarding how a field is created.

The application is about spend analysis, and there are suppliers which has invoices. And my application must export a file in which I should have a STATUS of each supplier. For example if a supplier has spends over 12 past months it is ACTIVE, else is FORMER.

The qlikview condition is :

if(rolling_spend_for_status<>0,'ACTIVE','FORMER') as SRM_STATUS

where rolling status is :

if(TimeKey>=((year(today())-1)*100+month(today())),sum(InvoiceLineAmountEUR)) as rolling_spend_for_status

and TimeKey is like '201501' (year and month).

A part of the result is fine. But there are some values (a lot) which has status ACTIVE and FORMER (both) and I don't know why.

See the picture. The invoice amount is 0 then SRM_STATUS should be 'FORMER" only.

*The last column is the old status. Is not taking in consideration.

Another example : The PID019707 should be ACTIVE only because has spends over 12 past months.

Do you have any idea why is this happening ? Why I have both Active and Former ?

**The rolling spend for status is in the chart as 'InvoiceAmount'.

***I cannot give you any sample files because there are too big.

Thanks!

[sujeetsingh]

that means it has two values and satisfies the both condition may be nulls.

[sunny_talwar]

In the attached image, you did not add rolling_spend_for_status can you reattach the image by adding rolling_spend_for_status into your table to see what values it is taking?

Also if possible, please attach your complete script.

Best,

Sunny

[#4]

The rolling spend status is Invoice Amount from the image. I forgot to mention.

[Anonymous]

Hi

attach some sample document.

[Peter_Cammaert]

Is this pseudo-code that you are using in your description? Because some things don't add up.

Anyway, add an ELSE clause to your rolling_spend_for_status calculation, like in

if(TimeKey>=((year(today())-1)*100+month(today())),sum(InvoiceLineAmountEUR), 0) as rolling_spend_for_status

[sasiparupudi1]

change the if condition to have a else part.

if(TimeKey>=((year(today())-1)*100+month(today())),sum(InvoiceLineAmountEUR),0)

hth

Sasi

[#8]

I've added the script. The data is taken from another qlikview application with binary load. So this is what I have.

[Peter_Cammaert]

The TimeKey test in your script either produces NULL or an amount (that could be 0). Then you go on and test for difference from 0. NULL will do weird things in such a test because you cannot predict comparisons of numerical values with non-numerical (non-existent) values.

Add an ELSE clause as suggested below.

[#10]

I changed it. Is the same thing.