4 Replies Latest reply: May 29, 2016 1:54 PM by Valentin Plekhanov RSS

    Make age, age range in script from SQL query

    Valentin Plekhanov

      Hi gurus

       

      Now I know to add age column in script, but next problem in age range in script

       

       

      LOAD *,

      age(today(),BirthDate) as Age;

      SQL SELECT AddressLine1,
      CustomerKey,
      DateFirstPurchase,
      EmailAddress,
      EnglishEducation,
      FirstName,
      LastName,
      Gender,
      Phone,
      BirthDate
      FROM AdventureWorksDW2014.dbo.DimCustomer;

      Now this scrip make new column as AGE

       

       

       

      but if I use this script is not working:

       

       

      LOAD *,

      age(today(),BirthDate) as Age,
      if(age(today(),BirthDate)<=25,dual('<=25'),
      if(age(today(),BirthDate)<=50,dual('26-50'),
      if(age(today(),BirthDate)<=60,dual('51-60'),
      if(age(today(),birthdate)>60,dual ('60+'))))) as AgeRange ;

      Error in expression:

      Dual takes 2 parameters

       

      Where problem ??

       

      Thanks

       

       

        • Re: Make age, age range in script from SQL query
          Sunny Talwar

          Try this:

           

          LOAD *,

              Age(Today(), BirthDate) as Age,
              If(Age(Today(), BirthDate) <= 25, Dual('<=25', 1),
              If(Age(Today(), BirthDate)<= 50, Dual('26-50', 2),
              If(Age(Today(), BirthDate)<= 60, Dual('51-60', 3),
              If(Age(Today(), BirthDate) > 60, Dual('60+', 4))))) as AgeRange;

          SQL SELECT AddressLine1,
          CustomerKey,
          DateFirstPurchase,
          EmailAddress,
          EnglishEducation,
          FirstName,
          LastName,
          Gender,
          Phone,
          BirthDate
          FROM AdventureWorksDW2014.dbo.DimCustomer;

          • Re: Make age, age range in script from SQL query
            Stefan Wühl

            If you encounter this kind of errors when executing the script, it's actually helpful to hit the HELP key and lookup the  function:

            Dual ‒ QlikView

             

            Here, you will clearly see the number of arguments required and expected:

             

            Dual - script and chart function

            Dual() combines a number and a string into a single record, such that the number representation of the record can be used for sorting and calculation purposes, while the string value can be used for display purposes

            Syntax:

            Dual(text, number)

            Return data type: dual

            Arguments:

            ArgumentDescription
            textThe string value to be used in combination with the number argument.
            numberThe number to be used in combination with the string in the string argument.

            In QlikView, all field values are potentially dual values. This means that the field values can have both a numeric value and a textual value. An example is a date that could have a numeric value of 40908 and the textual representation ‘2011-12-31’.

            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.