How to use- Dual()

    Have you ever wonder how the examples from the Qlikview help may look like?

    Please see below and enjoy responsibly...

     

    Dual()

     

    Definition:

    Forced association of an arbitrary string representation s with a given number representation x. In QlikView, when several data items read into one field have different string representations but the same valid number representation, they will all share the first string representation encountered. The dual function is typically used early in the script, before other data is read into the field concerned, in order to create that first string representation, which will be shown in list boxes etc.


    Syntax:

    where

    text - this is first column of data

    number- this is second column of data

     

    Qlikview has specific way of storing data. Each field is represented by Text and Number data type called dual.

    The Text format is visible to user and the Number is being used for calculation and sorting.

     


                        You can imagine that each field has top Text layer and bottom

                        (like salad in your Hamburger) Number layer.

     



    This association works most of the time and for most of the data type but there are situation that we need to force this association to work the way we want.

     

    Preparation

    Remember ligtbulb.jpg

    • To use the script below the csv file must be saved in the same folder that your qvd file


    Qlik help  example contains two tables: inline and from csv file(sample attached)

     

    load dual ( string,numrep ) as DayOfWeek inline

    [ string,numrep

    Monday,0

    Tuesday,1

    Wednesday,2

    Thursday,3

    Friday,4

    Saturday,5

    Sunday,6 ];


    Directory;

    LOAD Date,

        Sales

    FROM

    afile.csv

    (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

     

    //The last line  is not part of Qlikview help but you have to have this line to correctly load data into Qlikview

     


    after Reload we have:

    As you probably know when Values are on the left hand side they represent Text when on the right Number.

    So why we have Text on the right?

    In the background (during load) the numbers have been associated  with the text.

    So what you see is the bun what you do not is the underlay salad:

    Practical use:

     

    Example 1

     

     

     

    The simple but maybe not the most

    useful calculation

    is to add Days of the week from our data

    =sum(DayOfWeek)

     

     

    Example 2

    Counting days of the week:

     

    Example 3

    (please use attached xlsx file and below Script)

     

    A survey has been conducted and the data in Excel file looks like this:

    What we want is to assign more meaningful description to the rate numbers.

     

    Script:

     

    Data:

    mapping load * Inline

    [ Rate,Value

    Very Good,5

    Good,4

    Average,3

    Bad,2

    Very Bad,1

    ]

    ;

    Sort:

    load

    Responds,

    dual(Rate,ApplyMap('Data',Rate)) as Rate

     

    FROM

    Survey.xlsx

    (ooxml, embedded labels, table is Sheet1);

     

    Now we can create Straight Table or bar Bar Chart:

     

     

     

    This function can be use in many different ways.Please research our community to find more examples.

     

     

    Still feeling hungry?

    How to use - Only()

    Do you Qualify?- How to use QUALIFY statement

    How to use - Dimensionality()

    Missing Manual - GetFieldSelections() + Bonus Example

    MaxString & MinString - How to + examples

    The second dimension... or how to use secondarydimensionality()

    How to use - MissingCount()

    How to use- Dual()

    Expression Scene Investigation, E1: Moving Average