topic Re: Converting SQL to Qlik Sense in App Development

Converting SQL to Qlik Sense

brandon_d — Sat, 16 Nov 2024 01:47:22 GMT

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'

Re: Converting SQL to Qlik Sense

jwjackso — Tue, 28 Jul 2020 17:30:21 GMT

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.

Re: Converting SQL to Qlik Sense

brandon_d — Tue, 28 Jul 2020 17:52:06 GMT

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?

Re: Converting SQL to Qlik Sense

jwjackso — Tue, 28 Jul 2020 18:22:41 GMT

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

Re: Converting SQL to Qlik Sense

brandon_d — Tue, 28 Jul 2020 19:03:36 GMT

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