Get variable to update when Null values is selected

[shansen]

A lot of this description won't make much sense until you open the qvf file I am attaching.  Basically, I cannot get a Variable to set and display properly when it is populated from a selected value in a field, and that value is NULL.  Does anyone know how to do this?  I have been trying NullAsValue as a work-around, but haven't been able to get that to work yet either.

Here is more detail:
Problem 1: Click on a value in one of the tables, then click "Set vMy...Var to Selected My... Value". This works fine as long as you select a populated value. However if you select only the Null value in one of the tables, and then click "Set vMy...Var to Selected My... Value", then the value does not update.
Problem 2: When the "Set vMy...Var to Null" button is pressed, it shows First char code: 45 ('-'), rather than 0.
Both of these should populate the variable the way "vMy...Var Variable Input" does when the user selects a Null value. It should result in First char code: 0 (Null value). Is there a workaround for this? I need them to work this way so I can do the What-If Scenario Analysis shown here when my dataset has Null values. If I send anything else but Null() (Char code: 0) to get a real-time prediction, then the probability is altered. Problem 2 I can get around by sending Null() to the the ScriptEvalEx() function when the variable's value is '-'. However, Problem 1 makes it so the variable simply doesn't update when it should. Is there any way to force it to update when a Null value gets selected?

[rwunderlich]

I think you are confusion Null and Blank values. They are two different things. Null is a true missing value and it is represented in display by the character "-". Blank is a string with length of zero and it displays as "". 

If you want the same behavior, change your "Set to Null" button actions to set the value ="" instead of =Null().

For more info see https://community.qlik.com/t5/Member-Articles/NULL-handling-in-QlikView/ta-p/1484472

-Rob

[shansen]

Rob, Thanks for the reply.

I understand the difference between Blank and Null.  That's not the real problem here.  In the end, the output shown in the text boxes is going to be passed to the ScriptEvalEx() function to get a real-time prediction from AutoML.  That function imputes values where Nulls exist, and does not impute values for Blank values.  So, the answer returned from AutoML is different.  It must have Null values sent to get the right answer.  However, I can handle exceptions in my formulas.  The real problem is that the variable won't update at all when a Null value is selected.

The chain of widgets needed to make What-If Scenarios work is a bit more complex that what I show in the example Set Variable Test App.  A what-if sheet needs to allow the user to select a row in a feature table, update the values of variables based on that selection (hence the button), and then use Variable inputs to make custom modifications of those variables.  A production app with this process cannot break when there are Nulls in the dataset.  So, variables need to be able to update when there are Nulls.

The "Set ... to Null" buttons in my example app may be worth emulating in one respect - at least they cause the variable to update.  Those are included for comparison.  However, the process I need to work is this:

1. Select some non-Null value for MyString or MyNum.  Hit Set My...Var to Selected ... Value.  (It updates.)
2. Have a Null value selected for MyString or MyNum.  Hit Set My...Var to Selected ... Value.  Have it update.  (This is the part not working right now.)

On step 2 the variable values could update to anything.  I could have it update to "NULL" for MyStr, and to -1 for MyNum.  Then when I pass the value in to the ScriptEvalEx() function, I could handle those cases and send a Null() to the function.  But I can't even get an action to update the variable's value when the source data has a Null value.  Another alternative might be to use NullAsValue in the data load script, to replace Nulls in the dataset with some value that would cause the variable to update, but I haven't been able to get that to work either.  Unfortunatly I have not found a workaround on this yet.

[rwunderlich]

As you've suggested, you won't be able to pass a true null to ScriptEvalEx(). You will need to send a proxy like "NULL".

In your step 2: "Have a Null value selected for MyString or MyNum"

How can a user select a Null value in Qlik?

-Rob

[shansen]

Rob,

Yes, I can send a true Null to the ScriptEvalEx() function.  That is a recent development that Qlik just rolled out in response to one of my tickets to make the What-If Scenario analysis work with production data.  It is the only way to get a real-time prediction to return the same probability that it did when the training dataset was run when there are Nulls.  It has to accept Null values, and impute the same mean and mode values that it learned from the training dataset in order to produce the same probability.

To understand my step 2, it helps to have the Set Variable Test.qvf file open for reference. (I just realized that I did not make my Troubleshooting sheet public in the App.  Here it is again with that change.)  On the Troubleshooting sheet there are two Filter panes that show all the values available in the MyNum and MyString fields.  Before you can see that Step 2 doesn't work, you first need to do step 1: Select a non-Null value in one of those Filter panes, then hit the button above it labeled "Set My...Var to Selected ... Value".  The text boxes on the right below the button show what would be passed to the ScriptEvalEx() function.  Now clear the selection in the green box at the top of the App.  Then for Step 2, select a blank value from either the MyNum or MyString Filter pane, and hit the "Set My...Var to Selected ... Value" button above it.  You will see that the text at right does not update.  That is the problem.

[rwunderlich]

I think it may be related to how we delete variables in script -- by setting the variable to '' or null().  Because of that meaning, there may be some restriction in setting a variable to one of those values in the UI. Interesting question to take back to Qlik.

-Rob

[shansen]

Thanks for looking into it, Rob.  I know it is a complex issue.  I am just fishing for a workaround if it exists.  I will definitely coordinate with Qlik if not.

[rwunderlich · Accepted solution]

The only workaround I can think of is to use a placeholder like NULL in the data and then conditionally replace it with a null() in the ScriptEvalEx() expression.

-Rob

[shansen]

I was able to get this to work, by doing lines like these in the Data load editor for each incoming numeric and string field:
Alt(Num(MyNum), Dual('NULL',-999)) AS MyNum,
If(MyString='', 'NULL', MyString) AS MyString

The Num and Dual functions are how I helped Qlik recognize the MyNum field as numeric so the data type would match with what the ScriptEvalEx() function expects. This allows a button or other action to set a variable's value when the source data has a Null. Then in chart where the real-time prediction is used (Text & Image, KPI, table, etc.), I used a ScriptEvalEx() function call with the string and numeric variables passed in like this:

endpoints.ScriptEvalEx('NS...','{"RequestType":"endpoint", "endpoint":{"connectionname":"<Space:ConnectionName>","column":"<Prediction Column Name>"}}',
vMyNum AS MyNum,
If(vMyString='NULL', Null(), vMyString) AS MyString,
...
)

Thanks for all your help Rob.

[shansen]

I submitted this improvement idea to qlik:

https://ideation.qlik.com/app/#/case/365819?currentProductId=9da99bc1-0dfb-4471-8e12-d23685578c43