View Only
  • 1.  Pipeline Jinja2 Question

    Posted 10-04-2022 18:00
    Edited by Shane Miller 10-04-2022 18:09
      |   view attached
    I have 1 App and 3 tables that I believe I need to utilize to get the proper results from my pipeline:
    1) Claims Detail Extract
         a. Member ID (numeric)
         b. Employer ID (text)
         c. From DOS (date)
         d. To DOS (date)
         e. Paid Amount (numeric)
    2) Claims Account Report
         a. Employer ID (text)
         b. Master DOS Start (date)
         c. Master DOS End (date)
    3) Member Spec Alert
         a. Member ID Lookup (text (reference)) connected to another app/table
         b. Spec Amount (numeric)

    Essentially, I would like to create a pipeline that searches for and sums the [paid amounts](1e) of every unique [member ID](1a) whose
    [From DOS] (1c) is On or After [Master DOS Start] (2b)
    AND [To DOS] (1d) is On or before [Master DOS End] (2c)
    AND [employer ID](1b) equals [Employer ID] (2a)
    If TRUE create a record capturing the [member id](1a) and the summed [paid amounts](1e) on the table called Member Spec Alert.

    I am getting hung up on, well, most of it to be honest. I am unsure of which order to search for the records, whether or not to loop or not loop the record searches, and most of all, how to do the coding for the summation of the paid amounts based on those dates (using an if condition on pipeline?). 
    I have created many advanced queries that use this logic of: if Employer ID matches Employer ID, and dates are within the proper parameters, then sum these paid amounts. For whatever reason though, this jinja2 coding language isn't working. This is what I was trying to use in an if statement

    { c|sum(attribute="paid_amount") >= a.stop_loss_client_spec_notification_amount }

    c|sum being the claim detail extract table. Stoploss notification being a numeric lookup value which is pulled from "a." being the claims account report table

    When I ran this it was creating records on the Member Spec Alert (as it's supposed to), but the summed values were much smaller than the numeric lookup value that it should be greater than or equal to. Most of the time the summed value was zero, which may indicate it isn't summing the values of that member.
    Any recommendation or help would be much appreciated. I have posted a screenshot to add a visual aid.
    Thank you!

    Shane Miller

  • 2.  RE: Pipeline Jinja2 Question

    Posted 10-18-2022 22:15

    What is the Search criteria in Step B?   Are you sure that is getting the right child records when you execute the Sum?   

    It is easy to make a search mistake and have the next Step calculate incorrectly.

    Don Larson