Hello community,
I need to count the number of lines in our invoices and count the number of corrected lines in our invoices to report invoice compliance.
Here is a (URL Removed by Staff) of the big query source that I’m using (for which unfortunately I don’t have any rights, do not propose me to change the data source directly).
I’ve gave you several examples :
Lines 2-6 : invoices without correction, just count 1 for each _Invoice_Id_unique
Lines 8-10 : invoice has been refund and recharged but with the same amount => 1 line to count / 0 correction to count
Lines 12-14 : invoice has been refund and recharged with the different amount => 1 line to count / 1 correction to count
Lines 16-22 : invoice has been refund and recharged several time but with the same amount => 1 line to count / 0 correction to count
Lines 24-30 : invoice has been refund and recharged several time with a different amount at the end => 1 line to count / 1 correction to count.
At the end, I need to know, for each “_Invoice_Id_unique”, if the 1st “Revenue_invoiced_measure” is different that last “Revenue_invoiced_measure”, then it’s a correction, else no correction.
invoice_id is incremental, so we can use min/max of invoice_id to compare first Revenue_invoiced_measure and last Revenue_invoiced_measure
ChatGPT is not able to help neither Gemini, I’m going crazy going around in circles !
Any idea ?