Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

akuttler
New Contributor III

Left Join Script with multiple conditions

I have a table called patient ledger with a field for "item type" where "F-C" represents filed claim, and "C" represents Charges.

I am trying sum the total charges by ENCID (encounter id) and left join to the F-C rows by ENCID.  So I can know the total charges for each claim filed.

When I run the script below it never finishes loading the csv file gets really huge. I'm not sure why it is loading so much data...am I missing something?

Thanks in advance.

Claims_Submitted_2018:

LOAD

                  PracticeName,

                  ENCID as "Claim ID",

                 "Transaction Date" as "Date Submitted",

                 LocationName;

SQL SELECT *

FROM [patientledgerreport]

WHERE [Transaction Date] BETWEEN '01-01-2018' AND '01-31-2018'

AND [ITEMTYPE] IN ('F-C')

AND ([Description] NOT LIKE '%REFILE%'

AND [Description] NOT LIKE '%CROSSOVER%');

Left Join(Claims_Submitted_2018)

Load

Total_Charges as "ENCID Total Charges",

ENCID;

SQL SELECT

[ENCID],

SUM([Trans]) AS Total_Charges

                FROM [patientledgerreport]

AND [ITEMTYPE] IN ('C')

Group By [ENCID];

store Claims_Submitted_2018 into [lib://BACKUP/Claims_Submitted_2018.csv] (txt);

1 Solution

Accepted Solutions
siddharth_s3
Contributor

Re: Left Join Script with multiple conditions

I would suggest you to do an applymap since you have only 2 fields. Something like this.
Applymap is faster and rules out errors as well.

Claims_Submitted_2018:

Mapping Load

ENCID,

Total_Charges as "ENCID Total Charges";

SQL SELECT

[ENCID],

SUM([Trans]) AS Total_Charges

                FROM [patientledgerreport]

AND [ITEMTYPE] IN ('C')

Group By [ENCID];

store Claims_Submitted_2018 into [lib://BACKUP/Claims_Submitted_2018.csv] (txt);

Claims_Submitted_2018:

LOAD

                  PracticeName,

                  ENCID as "Claim ID",

                 "Transaction Date" as "Date Submitted",

                 LocationName,

Applymap('Claims_Submitted_2018',ENCID,0) as "ENCID Total Charges";

SQL SELECT *

FROM [patientledgerreport]

WHERE [Transaction Date] BETWEEN '01-01-2018' AND '01-31-2018'

AND [ITEMTYPE] IN ('F-C')

AND ([Description] NOT LIKE '%REFILE%'

AND [Description] NOT LIKE '%CROSSOVER%');

5 Replies
Digvijay_Singh
Honored Contributor III

Re: Left Join Script with multiple conditions

Looks like Your join is cross joining means not having matching field -

Check if you need this in bottom table as well -

ENCID as "Claim ID",

siddharth_s3
Contributor

Re: Left Join Script with multiple conditions

I would suggest you to do an applymap since you have only 2 fields. Something like this.
Applymap is faster and rules out errors as well.

Claims_Submitted_2018:

Mapping Load

ENCID,

Total_Charges as "ENCID Total Charges";

SQL SELECT

[ENCID],

SUM([Trans]) AS Total_Charges

                FROM [patientledgerreport]

AND [ITEMTYPE] IN ('C')

Group By [ENCID];

store Claims_Submitted_2018 into [lib://BACKUP/Claims_Submitted_2018.csv] (txt);

Claims_Submitted_2018:

LOAD

                  PracticeName,

                  ENCID as "Claim ID",

                 "Transaction Date" as "Date Submitted",

                 LocationName,

Applymap('Claims_Submitted_2018',ENCID,0) as "ENCID Total Charges";

SQL SELECT *

FROM [patientledgerreport]

WHERE [Transaction Date] BETWEEN '01-01-2018' AND '01-31-2018'

AND [ITEMTYPE] IN ('F-C')

AND ([Description] NOT LIKE '%REFILE%'

AND [Description] NOT LIKE '%CROSSOVER%');

akuttler
New Contributor III

Re: Left Join Script with multiple conditions

Thank you both solutions worked.

siddharth_s3
Contributor

Re: Left Join Script with multiple conditions

You are welcome.

Mark a solution as answered and close the thread as well please. Unless you have a follow up.

michael_anywar
Contributor

Re: Left Join Script with multiple conditions

I need some help as well.

I have 3 tables which have all been loaded and have various fields.

But now I wanty to make use of a few field form these tables by creating a new table through a join.

First all the tables are loaded, have gone through the select statements.....

Below is my load though of.

Activities_join:

NoConcatenate

Load

[fullName] AS [fullName],

[taskType] AS [taskType],

[StartTime] AS [StartTime],

[maufacturingOrderID] AS [ManufacturingOrderID]

Resident activities_root;


Left Join (Activities_join)

Load

[maufacturingOrderID] AS [ManufacturingOrderID],

[ProductName] AS [ProductName],

[quantity] AS [quantity]

Resident Order_Table;

Left Join(Products_Table)

NoConcatenate

Load

[ProductID] AS [ProductID],

[ProductName] AS [ProductName]

Resident Products_Table;

DROP Table Products_Table;

DROP Table Order_Table;