Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
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);
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.
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
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.