You are invited to a DUAL

[Dalton_Ruer]

This is probably the image that came to mind in the back of your head when you saw the word DUAL. But that's the beauty (or chaos) of the English language.

Since the invention of the computer there has been a duality between data that is computer friendly but user un-friendly or data that is user friendly but computer un-friendly. Binary (0's and 1's) is what the computer loves. While us humans seem to prefer text.

Sure there are some that are much more Data Dorky than me and they can read 010001011101001010101010101011010 as it come across, but me I like to read words like "True" or "Yes." Guess it's just the way I was brought up.

Dealing with 0's and 1's is super fast for the computer. Super fast for the computer means super responsive and customers do like speed in their systems. But if the data can't be consumed due to a lack of English words that's an issue. So you typically see the words "True/False" or "Yes/No" instead of the 0's and 1's. Nicety for the end user but then the computer is slower. It can handle expressions like "SUM( IF (SomeFlag = "Yes", 1, 0)" but when those involve hundreds of millions of rows it puts a strain on the CPU and nobody likes a slow computer.

Qlik understands this duality and has in fact created a DUAL datatype for just such cases when you want to achieve both goals. You want super fast, but you also want super user friendly. Seriously!

The DUAL datatype allows you to create a field that is displayed to the end user as TEXT but also retains a NUMERIC representation so the computer can operate super quickly. You can still display True/False for the end user while replacing the IF statement with a simple SUM ( SomeFlag). You've gotta like that. But your usage doesn't have to end there. Need a total of all of the costs associated with patients that have some flag set? Why not use simple math instead of a really complicated IF expression:

Sum ( SomeFlag * Cost)

That's CRAZY! Yes it is crazy cool. Anything multiplied by 1 is the number, while anything multiplied by 0 is zero. Now that's a fast way to get the answer you are looking for.

You can also use the DUAL datatype for special values that you want to sort in a certain order like DUAL ( MonthName, Month#) etc.

This video utilizes the General Hospital data set (Click here to get it yourself) and demonstrates several fields that ingest values that would require a lot of processing and speed the process up by using DUAL.

Please download the attached video and watch at your leisure. 

[luismadriz]

Thanks Dalton, as always your explanations are great!!!

My first app had the on-the-fly IFs for the flags and each sheet was taking around 10-20 seconds to load until I "fixed" the load script... but haven't used flags as Dual yet; I will from now on. Many thanks for the advice

Cheers,

Luis

[Dalton_Ruer]

Two reasons I LOVE this comment

1. You saw it before I even announced globally

2. I'm so excited that it was able to help you. I can't wait to here how fast things roll and how much your end user adoption grows as you use this.

Keep Qlik'ing and keep sharing.

[Lauri]

Thanks, this is very helpful!

Question: Can you tell Qlik to display the number rather than the text, or vice versa? For example, say I calculate a Max of a dual field, Qlik shows the numeric (I assume because that's what the Max function returns). I do indeed wan to get the Max numeric value, but I want to display the text part. Possible?

Grammatical side-note: The cowboys are have a duel.

Cinematic side-note: If you haven't seen Steven Spielberg's early movie, Duel, you should.

[Dalton_Ruer]

If you have a dual field but want to display the numeric value rather than the text somewhere you can simply use the expression NUM(MyDualField) and it will show the numeric value rather than the text value.

If you want to show the String for the MAX value you can do this: Text(MaxString( MyDualField))

Don't tell anyone but I almost didn't graduate high school because I couldn't pass the gammar portion of a test.