Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Make age, age range in script from SQL query

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

1 Solution

Accepted Solutions

Re: Make age, age range in script from SQL query

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;

4 Replies

Re: Make age, age range in script from SQL query

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;

MVP
MVP

Re: Make age, age range in script from SQL query

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.

Not applicable

Re: Make age, age range in script from SQL query

yes is stupid mistake

thanks

Not applicable

Re: Make age, age range in script from SQL query

thank now is work

Community Browser