Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the below data and format of the expected results. I don't know the right expression to use to pull the expected results. Basically, I am just trying to get the full name linked to the usernames for the end users. May be ONLY function might be the solution but I am not getting how to write the expression. I get the expected results by twisting the sql script by adding multiple joins but I am sure QlikView has an easier expression to do this.
Database info
TableA
AccountID | AttendingID | AdmittingID |
---|---|---|
1001 | jwalker | ptrump |
2001 | asmith | jwalker |
3001 | ptrump | asmith |
TableB
ProviderID | Name |
---|---|
jwalker | Walker, James |
ptrump | Trump, Peter |
asmith | Smith, Aliya |
SQL Script
SELECT TableA.AccountID, TableB.Name
FROM TableA
LEFT JOIN TableB
ON TableA. AttendingID =TableB. ProviderID AND TableA. AdmittingID =TableB.ProviderID
Expected Result - What expressions should I use to get the below result???
AccountID | AttendingName | AdmittingName |
---|---|---|
1001 | Walker, James | Trump, Peter |
2001 | Smith, Aliya | Walker, James |
3001 | Trump, Peter | Smith, Aliya |
Thanks ton.
See the attached qvw
See the attached qvw
Hi m w!
What you have done is divided TableA into Attending and Admitting tables and that is exactly what I meant by "I get the expected results by twisting the sql script by adding multiple joins". I am using straightbox so I was wondering if we can add some kind of expression that can solve this in a simpler way than doing much of coding in the script itself.
Expression to pick Name based on AttendingID and expression to pick Name based on AdmittingID.
Thanks.
I tried the below expressions in a StraightBox but it did not work.
Dimension: AccountID
Expression 1: IF(AttendingID=ProviderID, Name)
Expression 2: IF(AdmittingID=ProviderID, Name)
Any idea?
Thanks.
My opinion is that it is generally better to do the extra coding in script. Adding if statements in the UI slows down the application for the user.
Hi,
one solution could be:
mapProvNam:
Mapping LOAD * FROM [https://community.qlik.com/thread/220998] (html, codepage is 1252, embedded labels, table is @2);
TableA:
LOAD *,
ApplyMap('mapProvNam',AttendingID) as AttendingName,
ApplyMap('mapProvNam',AdmittingID) as AdmittingName
FROM [https://community.qlik.com/thread/220998] (html, codepage is 1252, embedded labels, table is @1);
hope this helps
regards
Marco
Hi All,
I was just trying to not do this in the code script itself but as an expression. Thank you though for different solutions. Here is the logic that I am going to use for now with my actual code.
SELECT TableA.AccountID, TableB.Name as AttendingName, C.Name as AdmittingName
FROM TableA
LEFT JOIN TableB
ON TableA.AttendingID =TableB.ProviderID
LEFT JOIN TableB as C
ON TableA.AdmittingID=C.ProviderID
StraightBox:
Dimension: AccountID
Expression1: AttendingName
Expression2: AdmittingName
Thank you all!