Need help creating Formula to select Invoice Number when it Contains an "A"

Options
jtrollinger
jtrollinger Member
in Magic ETL

I need help writing a Formula for an Aggregated Field in a Group by that will select the Latest Invoice that has an "A" in it if it exists. This example only has 1 Credit and Adjustment (C & A Invoice but I could have multiple to I need to take into consideration the Last Invoice Date as well)

image.png

Below is my attempt but I keep getting the First Invoice Number created.

image.png

Best Answers

  • MichelleH
    MichelleH Coach
    Answer ✓
    Options

    @jtrollinger Assuming you are defining the last invoice as the record with the most recent InvoiceDate, then you will need two steps to accomplish this:

    1. In the same Group by tile, include this formula to identify the most recent date having an invoice with "A" in the number: MAX(case when `InvoiceNo` like '%A%' then `InvoiceDate` end)
    2. Create a separate branch of the dataflow before the Group by that filters to just rows with an Invoice Number containing "A" (`InvoiceNo` like '%A%'). Then join this back to the original branch after the group by on same fields you used in the grouping.

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓
    Options

    Does it work if you take out the "ELSE" clause? Right now, it points InvoiceNo no matter what (when it contains 'A', else when it does not contain 'A').

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

Answers

  • MichelleH
    MichelleH Coach
    Answer ✓
    Options

    @jtrollinger Assuming you are defining the last invoice as the record with the most recent InvoiceDate, then you will need two steps to accomplish this:

    1. In the same Group by tile, include this formula to identify the most recent date having an invoice with "A" in the number: MAX(case when `InvoiceNo` like '%A%' then `InvoiceDate` end)
    2. Create a separate branch of the dataflow before the Group by that filters to just rows with an Invoice Number containing "A" (`InvoiceNo` like '%A%'). Then join this back to the original branch after the group by on same fields you used in the grouping.

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓
    Options

    Does it work if you take out the "ELSE" clause? Right now, it points InvoiceNo no matter what (when it contains 'A', else when it does not contain 'A').

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

Categories