5 Replies Latest reply: Mar 1, 2018 3:25 AM by Michael Anywar RSS

    Left Join Script with multiple conditions

    Ashley Kuttler

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

        • Re: Left Join Script with multiple conditions
          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",

          • Re: Left Join Script with multiple conditions
            Siddharth Sheshadri

            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%');
            
            
            • Re: Left Join Script with multiple conditions
              Ashley Kuttler

              Thank you both solutions worked.

                • Re: Left Join Script with multiple conditions
                  Siddharth Sheshadri

                  You are welcome.

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

                    • Re: Left Join Script with multiple conditions
                      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;