topic Re: Join two tables. in QlikView

Join two tables.

Fri, 20 Mar 2015 10:53:22 GMT

Hi,

How can I join both tables into 1 table, see the script underneath?

Kind regards

[Employees New]:

LOAD *,

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

If( num([Year Salary]/12)<= 1000,'0-1K',

If( num([Year Salary]/12)>1000 and num([Year Salary]/12)<=2000,'1-2K',

If( num([Year Salary]/12)>2000 and num([Year Salary]/12) <=3000,'2-3K',

If( num([Year Salary]/12)>3000 and num([Year Salary]/12)<=4000,'3-4K',

If( num([Year Salary]/12)>4000,'4K>',''))))) as 'Month Salary Grouped',

[First Name] & ' ' & [Last Name] as Name

FROM

Datasources\Employees_New.xls

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

[Employees off]:

LOAD *,

Age(Today(),[Date of Birth]) as 'Years Old',

If( Age(Today(),[Date of Birth]) <= 18,'0-18',

If( Age(Today(),[Date of Birth]) >18 and Age(Today(),[Date of Birth]) <26,'19-25',

If( Age(Today(),[Date of Birth]) >25 and Age(Today(),[Date of Birth]) <36,'26-35',

If( Age(Today(),[Date of Birth]) >35 and Age(Today(),[Date of Birth]) <50,'36-49',

If( Age(Today(),[Date of Birth]) >49,'50+',''))))) as 'Years Old Grouped',

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

If( num([Year Salary]/12)<= 1000,'0-1K',

If( num([Year Salary]/12)>1000 and num([Year Salary]/12)<=2000,'1-2K',

If( num([Year Salary]/12)>2000 and num([Year Salary]/12) <=3000,'2-3K',

If( num([Year Salary]/12)>3000 and num([Year Salary]/12)<=4000,'3-4K',

If( num([Year Salary]/12)>4000,'4K>',''))))) as 'Month Salary Grouped',

[First Name] & ' ' & [Last Name] as Name

FROM

Datasources\EmpOff.xls

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

Re: Join two tables.

sagarkharpude — Fri, 20 Mar 2015 10:56:30 GMT

use 'concatenate' between this 2 tables as the most of the columns are same

Re: Join two tables.

Fri, 20 Mar 2015 11:10:54 GMT

Hi,

Try this script.

[Employees off]:

LOAD *,

Age(Today(),[Date of Birth]) as 'Years Old',

If( Age(Today(),[Date of Birth]) <= 18,'0-18',

If( Age(Today(),[Date of Birth]) >18 and Age(Today(),[Date of Birth]) <26,'19-25',

If( Age(Today(),[Date of Birth]) >25 and Age(Today(),[Date of Birth]) <36,'26-35',

If( Age(Today(),[Date of Birth]) >35 and Age(Today(),[Date of Birth]) <50,'36-49',

If( Age(Today(),[Date of Birth]) >49,'50+',''))))) as 'Years Old Grouped',

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

If( num([Year Salary]/12)<= 1000,'0-1K',

If( num([Year Salary]/12)>1000 and num([Year Salary]/12)<=2000,'1-2K',

If( num([Year Salary]/12)>2000 and num([Year Salary]/12) <=3000,'2-3K',

If( num([Year Salary]/12)>3000 and num([Year Salary]/12)<=4000,'3-4K',

If( num([Year Salary]/12)>4000,'4K>',''))))) as 'Month Salary Grouped',

[First Name] & ' ' & [Last Name] as Name

FROM

Datasources\EmpOff.xls

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

//[Employees New]:

left join([Employees off])

LOAD *,

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

If( num([Year Salary]/12)<= 1000,'0-1K',

If( num([Year Salary]/12)>1000 and num([Year Salary]/12)<=2000,'1-2K',

If( num([Year Salary]/12)>2000 and num([Year Salary]/12) <=3000,'2-3K',

If( num([Year Salary]/12)>3000 and num([Year Salary]/12)<=4000,'3-4K',

If( num([Year Salary]/12)>4000,'4K>',''))))) as 'Month Salary Grouped',

[First Name] & ' ' & [Last Name] as Name

FROM

Datasources\Employees_New.xls

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

Re: Join two tables.

jyothish8807 — Fri, 20 Mar 2015 11:55:10 GMT

Hi Randy,

What all field are you calling through " * " in both the tables??

Do you have a primary key for both?

Regards

Re: Join two tables.

buzzy996 — Fri, 20 Mar 2015 12:44:36 GMT

functionally what i understood,u suppose to be add ur all new employees to ur employee off table.

solution: if new employee id is not exists on "employee off " add here.

tht's simple and good solutions i believe.