Left Join Script with multiple conditions

[akuttler]

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);

[Digvijay_Singh]

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 · Accepted solution]

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]

Thank you both solutions worked.

[siddharth_s3]

You are welcome.

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

[michael_anywar]

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;