Keep first occurrence of value and set subsequent values to 0

Crisocir
Crisocir Member
in Magic ETL

I'm working with invoice data that has multiple line items for each invoice. The issue is that the tax field has the entire invoice tax value on each line item as shown below:

| Invoice | Item | Amount | Tax |
|---------|------|--------|-----|
| 1       | A    | 10     | 6   |
| 1       | B    | 20     | 6   |
| 1       | C    | 30     | 6   |

What I would want

| Invoice | Item | Amount | Tax |
|---------|------|--------|-----|
| 1       | A    | 10     | 6   |
| 1       | B    | 20     | 0   |
| 1       | C    | 30     | 0   |

So essentially I want to keep the tax value on the row with the first occurrence of the invoice number and then every occurrence of that invoice number after I want to set the tax value to 0. Is this even possible in ETL or SQL. 

Comments

  • GrantSmith
    GrantSmith Coach

    Hi @Crisocir 

     

    You can do this utilizing a window function (If you don't have it in your instance you'd need to talk to your CSM to get it enabled).

     

    Essentially this beast mode is calculating the row numbers (within each invoice) and then checking to see if it's the first row, if so return the tax amount otherwise return 0.

     

    CASE WHEN SUM(SUM(1)) OVER (PARTITION BY `Invoice` ORDER BY `Item`) = 1 THEN `Tax` ELSE 0 END

     

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    I would restructure this data in ETL.

    If Tax is the same across all invoice lines, take your Invoice Lines and Remove Tax.  then add a column with a fixed value of "Invoice Lines.

     

    Then UNION a copy of the data with one row per Invoice for Tax. you can put amount into the same column or have a separate column for tax amount.  add a fixed column with the value 'Tax Lines' 

     

    You can run with the Window'ed' function hack, but it's dependent on you showing each invoice line in your visualizations which may not be suitable for higher level consolidations.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories