topic Joining two sets of data together? in QlikView

Joining two sets of data together?

james_hanify — Sat, 10 Dec 2016 00:26:11 GMT

Hi all,

Bit confused how I can do this, the name in particular is in two different dictionaries, but may not be in the same, for example DZI does not exist with heir name in Users, but is in Salesman, and this will incur duplicates if i just Concatenate them.

Salesman

SalesCodeKey	SalesCodeName	UserKey	SalesmanGroup
W2	Dones Zhi	DZI	A
12	Jimbo Hanes	JHA	B

User

UserKey	UserName
JHA	Jimbo Hanes
RTO	Rudolf Tones

Combined:

UserKey	UserName	SalesmanCodeKey	SalesmanGroup
DZI	Dones Zhi	W2	A
JHA	Jimbo Hanes	12	B
RTO	Rudolf Tones	Null	Null

Example Code:

Users:
load
Z_ID as UserKey,
NAME as UserName;

SQL SELECT *
FROM SY_OPERATOR;

Salesman:
load
Z_ID as SalesCodeKey,
USER_ID_C as UserKey,
SALESM_NAME as Username,
SALESMAN_GROUP_C as SalesmanGroup;

SQL SELECT *
FROM "SM_SALESMAN";

Thanks,

James

Re: Joining two sets of data together?

sunny_talwar — Sat, 10 Dec 2016 01:26:48 GMT

May be this:

Table:

LOAD SalesCodeKey,

SalesCodeName as UserName,

UserKey,

SalesmanGroup

FROM [https://community.qlik.com/thread/242933]

(html, codepage is 1252, embedded labels, table is @1);

Join (Table)

LOAD UserKey,

UserName

FROM

[https://community.qlik.com/thread/242933]

(html, codepage is 1252, embedded labels, table is @2);

Re: Joining two sets of data together?

vinieme12 — Sat, 10 Dec 2016 14:45:45 GMT

Or Even Concatenate with NOT EXIST()

Table:

LOAD SalesCodeKey,

SalesCodeName as UserName,

UserKey,

SalesmanGroup

FROM [https://community.qlik.com/thread/242933]

(html, codepage is 1252, embedded labels, table is @1);

Concatenate

LOAD UserKey,

UserName

FROM

[https://community.qlik.com/thread/242933]

(html, codepage is 1252, embedded labels, table is @2)

WHERE NOT EXISTS(UserKey);

Re: Joining two sets of data together?

james_hanify — Sat, 10 Dec 2016 20:31:16 GMT

Many thanks both, i don't think it quite is what I need yet, this is the reason why I want to put it in the bigger User table, despite it looking like the child of it.

A salesperson may or may not be related to a User, a user may be deleted, in which case, the id needs to come from the salesperson table but there are more associative properties to the User but just not being used in this case.

When I try join, there were a few instances where the UserKey occured twice and on the concatenate, I couldn't get the exists to work with my SQL QV generator.

Re: Joining two sets of data together?

vinieme12 — Sun, 11 Dec 2016 09:36:40 GMT

A salesperson may or may not be related to a User,

What does this mean? can a salesperson have 0 or Many UserKey?

perhaps a detailed sample data that exhibits the problem will help us help you

Re: Joining two sets of data together?

james_hanify — Tue, 13 Dec 2016 23:06:34 GMT

Hi Vineeth, sorry for not getting back quickly. As part of a user clean up, which I didn't think was a good idea, but it was done anyway, was that some users are deleted from the system meaning that some of these salesman may exist as salesman but not as users, so therefore I was going to pull the name from the salesman record if the users didnt exist, however, I found another solution using the applymap idea.

Here is a question though, i've been using applynames for simple tables that are key, description as i didn't see the point in linking a table just for that data, if there was more than that I would. This helped when you had multiple going to the same key, however, what do you do if you have to go to that key multiple times? I was going to use the key as another name but then it creates them junction object syns, is there any other way around it, i've just another applymap but there is some structure that may be helpful for me to have.