You are invited to a DUAL

    Dual.jpgThis 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.