Link &Transform Expression

[Anonymous]

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.

[m_woolf · Accepted solution]

See the attached qvw

[Anonymous]

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.

[Anonymous]

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.

[m_woolf]

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.

[MarcoWedel]

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

[Anonymous]

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!