topic Re: IF condition takes both values in QlikView

IF condition takes both values

Thu, 10 Sep 2015 09:44:37 GMT

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!

Re: IF condition takes both values

sujeetsingh — Thu, 10 Sep 2015 09:50:17 GMT

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

Re: IF condition takes both values

sunny_talwar — Thu, 10 Sep 2015 09:50:40 GMT

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

Re: IF condition takes both values

Thu, 10 Sep 2015 09:54:02 GMT

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

Re: IF condition takes both values

Anonymous — Thu, 10 Sep 2015 09:59:48 GMT

attach some sample document.

Re: IF condition takes both values

Peter_Cammaert — Thu, 10 Sep 2015 10:01:24 GMT

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

Re: IF condition takes both values

sasiparupudi1 — Thu, 10 Sep 2015 10:04:29 GMT

change the if condition to have a else part.

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

hth

Sasi

Re: IF condition takes both values

Thu, 10 Sep 2015 10:08:33 GMT

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

Re: IF condition takes both values

Peter_Cammaert — Thu, 10 Sep 2015 10:32:51 GMT

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.

Re: IF condition takes both values

Thu, 10 Sep 2015 10:39:20 GMT

I changed it. Is the same thing.

Re: IF condition takes both values

Thu, 10 Sep 2015 10:39:25 GMT

I changed it. Is the same thing.

Re: IF condition takes both values

Thu, 10 Sep 2015 10:42:47 GMT

I've added an else clause, and is the same thing. What you mean by 'test for difference from 0' ? How should I do that ?

Re: IF condition takes both values

Peter_Cammaert — Thu, 10 Sep 2015 10:48:58 GMT

Ok, another track. Your resident table probably grows unexpectedly. The LEFT JOINs may be the culprits here. Let's investigate...

In your script, after the initial LOAD of REGULAR_SRM_INTERFACE_TMP1 and after every subsequent LEFT JOIN add the following code:

LET vNumRows = NoOfRows('REGULAR_SRM_INTERFACE_TMP1');

TRACE >>> Step 1: Number of Rows = $(vNumRows) <<< ;

Reload and watch the progress window.

Peter

Re: IF condition takes both values

Thu, 10 Sep 2015 11:39:47 GMT

CRATE_TMP << SUPPLIER_GROUPS 213.912 lines fetched

SUPPLIERS 356.395 lines fetched

INVOICES 85.717 lines fetched

CRATE << CRATE_TMP 42.249 lines fetched

MAP_CRATE << CRATE 42.249 lines fetched

MAP_SPEND << CRATE 42.249 lines fetched

REGULAR_SRM_INTERFACE_TMP1 << SUPPLIER_GROUPS 213.912 lines fetched

SUPPLIERS 356.395 lines fetched

>>> Step 1: Number of Rows = 399519 <<<

INVOICES 1.291.607 lines fetched

>>> Step 1: Number of Rows = 1534303 <<<

BUs 10.735 lines fetched

>>> Step 1: Number of Rows = 1534303 <<<

This is the output.

Re: IF condition takes both values

Peter_Cammaert — Thu, 10 Sep 2015 12:40:32 GMT

Aside from the ones listed, there are various other joins in your script that add columns to your tables, do GROUP BY reductions and rearrange the data in your basic set. Are you including all fields that identify separate internal table records for the same SupplierKey/TimeKey-combination in your Pivot table?

Or, to put it another way, if you create a Table box with fields SRM_PID, TimeKey and SRM_STATUS and select one of those TimeKey & SRM_PID combinations that provide faulty information in your Pivot table, how many SRM_STATUS values do you get? Which additional field(s) do you have to add to this table box to have another value difference?

Peter

Re: IF condition takes both values

Peter_Cammaert — Thu, 10 Sep 2015 12:42:48 GMT

To put it more clearly: there is another field that you do not include in your Pivot table and that has different values for those ACTIVE/FORMER lines whose other field values are all the same. Try to figure out which field is missing.

Peter

Re: IF condition takes both values

Thu, 10 Sep 2015 14:51:33 GMT

I've attached a sample document. I was able to store the final table into a qvd. The issue is that TimeKey is null for a lot of SRM_PIDs and some SRM_PIDs has TimeKey but one empty. And if I calculate the Status it will give me ACTIVE for TimeKey and FORMER for empty TimeKey. How can I handle these null values specially for those SRM_PIDs which has TimeKey and one null TimeKey ?

Re: IF condition takes both values

richard — Thu, 10 Sep 2015 15:03:49 GMT

Just use the best practice way of aggregations:

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

The result should be a numeric result 0, where after you can use:

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

Re: IF condition takes both values

Peter_Cammaert — Fri, 11 Sep 2015 08:04:11 GMT

Well, I guess you first have a choice to make: will you live with the Null-Timekey values or should you try to figure out why they are Null in some cases and correct that? I would go for the second option,

Re: IF condition takes both values

Fri, 11 Sep 2015 08:38:45 GMT

Yes. I found out why I have null values. It is because there are some SRM_PID which is loaded from another application where does not exist TimeKey correspondents to it. Thanks.