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!

[#2]

I changed it. Is the same thing.

[#3]

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 ?

[Peter_Cammaert]

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

[#5]

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.

[Peter_Cammaert]

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

[Peter_Cammaert]

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

[#8]

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 ?

[richard]

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

[Peter_Cammaert]

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,

[#11]

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.