topic Re: Left inner join and get only null rows in QlikView

Left inner join and get only null rows

Wed, 09 Jan 2013 13:10:21 GMT

Hi,

We would like to get users' job name and the result should give us jobname 'NoJob' unless the user has not job name How could we do that in qlickview.

Thanks

Users

ID	Name
1	Alan
2	Jack
3	Aly

UsersJob

ID	UserId	JobName
1	1	lkjlklj
2	3	sdfsdfsdf

SELECT	Users.ID,Users.Name,isnull(JobName,'NoJob') as JobName'
FROM	Users LEFT OUTER JOIN
	UsersJob ON Users.ID = UsersJob.UserID

Re: Left inner join and get only null rows

Gysbert_Wassenaar — Wed, 09 Jan 2013 13:19:44 GMT

Try using a mapping table and applymap:

JobMap:

mapping load UserId,JobName

from ...somewhere...;

Users:

load ID, Name, applymap('JobMap',ID,'NoJob') as JobName

fro ...somewhereelse...;

Re: Left inner join and get only null rows

Wed, 09 Jan 2013 13:21:46 GMT

First make qvds : Users & Userjob from the Db tables

then,

Users:

mapping load * from Userjob.qvd;

JobNames:

Load *,

applymap('Users',ID,'NoJob') as JobName

from Users.qvd;

Regards