Summary: I review a subtle difference between using Num() in the script vs Num() in charts. I make mistakes so you don’t have to 🙂
2020 marks my 13th year blogging about Qlik. I’m still learning, and still making mistakes! I’ll review a problem I created for myself recently, with the hope that you won’t have to repeat the exercise.
Here is a simplified example of the problem, enough to demonstrate the issue: This is the starting data table.
We have some number of “Metric” and “Value” along with fields that describe how the data should be formatted for display. The “Format” field contains a Qlik format specification. Format may be used in a Num() function as shown in the “Num(Value, Format)” measure in this table. Output is as expected, so far so good.
In my project, it became required to format the Value in the script. I moved the Num() measure to the script like this:
Num(Value, Format) as ValueFormatted
I expected ValueFormatted to yield the same result as the chart measure. Adding “ValueFormatted” to the chart yields this:
The results are not the same. ValueFormatted for “Days to ship” is incorrect. The other values are correct.
Let’s introduce another variation. This time I’ll sort the input when I create ValueFormatted.
Left Join(Data) Load *, Num(Value, Format) as ValueFormatted Resident Data Order by Metric Desc ;
Now “Customers per location” is incorrect and the other values are correct! What gives?
The Num() function returns a Dual() value . Duals have both a string (display) and a numeric value. When populating a data model field, Qlik will use a single string representation for a given numeric value for that field. The string selected will be the first encountered for that numeric value.
“Customers per location” and “Days to ship” shared the numeric value 4. In the data model, one or the other string representation — “4” or “4 days” — will be used, depending on which one is created first.
To get the correct results in this scenario — that is, unique strings dependent on Format — add the Text() function to extract the string at runtime.
Text(Num(Value, Format)) as ValueFormatted
Resolution came of course after I took the advice of my colleague Barry to “take a walk”.
I hope my story might save you some trouble. Happy scripting!
-Rob
You will find this as well when you load numeric values that you want to display as they come from the database. For instance, you might have a number 1001 in one row and Qlik will store it as 1001. A few rows later you might get another number as 001001. If all the values in the column are numbers, Qlik will interpret that 001001 value as 1001, and store it as 1001 even if it comes as 001001 from the database. To solve that you’ll have to use Text() when loading the field.
And that’s really annoying when the field is a product or ledger code and 1001 has a different meaning to 001001.
It can also get tricky when loading back in from QVDs and creating mapping tables when Qlik will re-interpret the data, so even though you converted it to text in one process it gets re-interpreted as a number. You end up either wrapping everything in Text([Field]) or appending a text character to the code which is ugly but works.
I came across this exact behaviour with dates when I tried to format one of them. I was unsure why the format of the date in the fact table was not set to what I wrote as Date( somedate, ‘DD/MM/YYYY’) as [Invoice Date] in load script. I did not notice though that [Invoice Date] field was created in other tab earlier in script where there was a [Invoice Date] loaded as dual to show some dates as “Today”, “Yesterday” and most of others formatted as ‘DD-MM-YYYY’.
thanks for sharing Rob!