topic Create interval fields in QlikView

Create interval fields

Wed, 18 Mar 2015 15:03:23 GMT

Hi All,

I would like to create interval fields for age (0-18, 19-25, 26-35, 36-49, 50+) and month salary (0-1K, 1-2K, 2-3K, 3-4K, 4K>). Can anyone tell me how to do this and if this should be done the load script or on some other place.

Thank you in advance.

[Employees New]:

Directory;

LOAD *,

num([Year Salary]/12) as 'Month Salary'

FROM

Datasources\Employees_New.xls

(biff, embedded labels, table is Employee$);

[Employees off]:

Directory;

LOAD *,

Age(Today(),[Date of Birth]) as Age,

DATE(DATE([Date of Birth], 'D-MM-YYY'), 'DD-MMM-YY') as Birthday,

Money(num([Year Salary]/12),'€#.##0') as 'Month Salary'

FROM

Datasources\EmpOff.xls

(biff, embedded labels, table is Employee$);

Kind regards

Re: Create interval fields

Gysbert_Wassenaar — Wed, 18 Mar 2015 15:11:25 GMT

For the monthly salaries you can probably use the class function since the intervals seem to be the same size. You can use the class function in the script and in expression. Doing the calculations in the script is the better option.

For the age classes you can either use a nested if statement or create a table with the intervals and then use the intervalmatch function to match the ages with the correct interval. See this blog post: IntervalMatch

Re: Create interval fields

buzzy996 — Wed, 18 Mar 2015 15:40:15 GMT

U can try this in ur script,

If( Age <= 18,'0-18',

If( Age >18 and Age <26,'19-25',

If( Age >18 and Age <26,'26-35',

If( Age >18 and Age <26,'36-49',

If( Age >49,'50+',''))))) as AgeGroup

If( Salary<= 1000,'0-1K',

If( Salary>1000 and Salary<=2000,'1-2K',

If( Salary>2000 and Salary <=3000,'2-3K',

If( Salary>3000 and Salary<=4000,'3-4K',

If( Salary>4000,'4K>',''))))) as salaryGroup