Keep first occurrence of value and set subsequent values to 0

Crisocir

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. 

GrantSmith

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

 

jaeW_at_Onyx

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.