Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
star_limit
Creator II
Creator II

Link &Transform Expression

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

AccountIDAttendingIDAdmittingID
1001jwalkerptrump
2001asmithjwalker
3001ptrumpasmith


TableB

ProviderIDName
jwalkerWalker, James
ptrumpTrump, Peter
asmithSmith, 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???

AccountIDAttendingNameAdmittingName
1001Walker, JamesTrump, Peter
2001Smith, AliyaWalker, James
3001Trump, PeterSmith, Aliya

Thanks ton.

1 Solution

Accepted Solutions
m_woolf
Master II
Master II

See the attached qvw

View solution in original post

6 Replies
m_woolf
Master II
Master II

See the attached qvw

star_limit
Creator II
Creator II
Author

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.

star_limit
Creator II
Creator II
Author

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
Master II
Master II

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:

QlikCommunity_Thread_220998_Pic1.JPG

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

star_limit
Creator II
Creator II
Author

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!