Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Contributor 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
Highlighted
Honored Contributor II

Re: Link &Transform Expression

See the attached qvw

View solution in original post

6 Replies
Highlighted
Honored Contributor II

Re: Link &Transform Expression

See the attached qvw

View solution in original post

Highlighted
Contributor II

Re: Link &Transform Expression

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.

Highlighted
Contributor II

Re: Link &Transform Expression

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.

Highlighted
Honored Contributor II

Re: Link &Transform Expression

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.

Highlighted

Re: Link &Transform Expression

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

Contributor II

Re: Link &Transform Expression

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!