Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
WE ARE LISTENING! New Navigation for Qlik Community, Sept. 26: TELL ME MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
brandon_d
Contributor III
Contributor III

Converting SQL to Qlik Sense

I am trying to recreate a SQL script into Qlik Sense within the Data load editor. I have an extraction layer that is pulling all of the tables that I need from the database, but I am uncertain how this (example below) would be written into the Data load editor within Qlik Sense. Below is the current portion of the SQL script that I am stuck on.

SELECT payer.PayerID
FROM t_VisitInsurance 
JOIN t_Payer payer on t_VisitInsurance .PayerID = payer.PayerID
WHERE t_VisitInsurance .VisitKey = v.VisitKey
AND t_VisitInsurance .PrimaryPayer='1') as 'Primary Payer ID'

 

 

Labels (4)
4 Replies
jwjackso
Specialist III
Specialist III

SomeName:

load *;

SQL SELECT payer.PayerID
FROM t_VisitInsurance 
JOIN t_Payer payer on t_VisitInsurance .PayerID = payer.PayerID
WHERE t_VisitInsurance .VisitKey = v.VisitKey
AND t_VisitInsurance .PrimaryPayer='1') as 'Primary Payer ID';

 

Keep in mind, that within a Qlik table (SomeName), each field has to be a unique name and field names are case sensitive.

brandon_d
Contributor III
Contributor III
Author

Thank you for your response. However, I do not want it to be in SQL, so how would this be done in standard Qlik language? 

jwjackso
Specialist III
Specialist III

If you had 2 tables

t_VisitInsurance :

load PayerID,

         PrimaryPayer;

 

Payer:

load PayerID;

In QLIK the tables would be joined on PayerID, so it depends on what type of calculation you are doing.  For a simple PayerID KPI,

 

Count({<PrimaryPayer={1}>}PayerID)

The Set Analysis {<PrimaryPayer={1}>} limits the data to PayerIDs with PrimaryPayer = 1

brandon_d
Contributor III
Contributor III
Author

The issue though is that there is a third table that only appears within
the Where clause.