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

Make age, age range in script from SQL query

Sat, 28 May 2016 17:05:33 GMT

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 — Sat, 28 May 2016 23:05:48 GMT

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

swuehl — Sat, 28 May 2016 23:53:35 GMT

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:

Argument	Description
text	The string value to be used in combination with the number argument.
number	The 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.

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

Sun, 29 May 2016 17:54:44 GMT

yes is stupid mistake

thanks

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

Sun, 29 May 2016 17:55:35 GMT

thank now is work