Forum Discussion

ShaneMiller1's avatar
Qrew Cadet
2 years ago

Pipeline Jinja2 Question

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

1 Reply

  • DonLarson's avatar
    Qrew Commander

    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