Alert KF Not working and giving SQL error?

[Former Member]

Hello All,

I have configured Below Alert KF and while pulling this into Planning view I am getting below SQL error.

[Irmi_Kuntze · Accepted solution]

1) Your formula does not catch it correctly, try this (might need some rework, but you should be able to catch my meaning)

IF(
(ISNULL("CEDUNCONSTRCONSFCST@MTHPRODLOCCUST") OR "CEDUNCONSTRCONSFCST@MTHPRODLOCCUST" = 0) AND (ISNULL("CEDUNCONSTRCONSFCSTPC@MTHPRODLOCCUST") OR "CEDUNCONSTRCONSFCSTPC@MTHPRODLOCCUST" = 0),
99999,
IF(
ABS("CEDUNCONSTRCONSFCST@MTHPRODLOCCUST" - "CEDUNCONSTRCONSFCSTPC@MTHPRODLOCCUST") / (("CEDUNCONSTRCONSFCST@MTHPRODLOCCUST" + "CEDUNCONSTRCONSFCSTPC@MTHPRODLOCCUST") / 2) > 0.5,
1, 0 )
)

2) You still did not share the VALUES of the input KF, as described in my last reply. I will not reply any more without the values 🙂

3) Can you please re-post a proper full screenshot of the config of your input KF's? The last screenshots from them looked a bit weird because in the header it stated it was the input KF, but the formula had seem to be from the target KF.

[Irmi_Kuntze]

Hi Rahul

you must ALWAYS catch devisions by zero in your formula, as this can always lead to issues. Not sure if that is the root cause for your case as I dont have the full picture of the values.

But you can always pull the values by creating a planning view that ONLY contains the input KF, or by using the master data workbooks and within that the planning combination view.

Before you reply, make sure that you

1) have corrected the formula

2) in case you still get an error provide a screenshot of the input values as described above

If you dont provide that, any further analysis will not be possible

Yours, Irmi

[Former Member]

Hello Irmi,

Thanks for your support. well per your suggestion I have made the below calculation to handle "0" but still getting the Sql error while adding that as a Alert in a planning book.

[Former Member]

Hello Irmi,

Sorry but after testing further this alert KF is making all the values RED, means its alerting for all the below values now which is wrong.

please see the below Screenshot:

[Irmi_Kuntze]

it may

You can check by log on without immediately pull the resulting kf, and only check the input

However, you beed to adjust your formula in any case, so just build in an if clause checking for sum of the input equalingbzero and setting a specific result in that case, else result is your initial formula

Than try again

[Former Member]

ok I will try to change that. Also I am getting same SQL error while running simulation when I add below KF at the planning view:

[Irmi_Kuntze]

I meant the value if the input, not the configuration

You have a formula where you do not catch how to treat deviding by zero which would lead to infinite result and hence throw error

So, is in your example the sume of both input kf = 0?

[Former Member]

I cant even put any value. the moment I add this alert and press OK it gives me hard SQL error?

do you think it may be because of the 0?

[Irmi_Kuntze]

i cannot see the values of your two input kr, but if both are either 0 or NULL it would fail because you do not catch devision by zero

[Former Member]

Here are my two other KF's: