- Mark as New
- Bookmark
- Subscribe
- Permalink
- Email to a Friend
- Report Inappropriate Content
I am running an export query with all the invoice items for a specific timeframe.
However I have many duplicated values that should not be there. After some digging, I found out that this happens due to our Rate Plan.
Is there any way I can deduplicate my invoice items knowing about this condition?
I've tried many alternatives on the where clause, with no success.
Solved! Go to Solution.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Permalink
- Email to a Friend
- Report Inappropriate Content
All of the data sources, including invoice item, report all the selected fields for all returned rows. So if I'm querying invoice items and inlcude the rate plan name in the results AND I have a subscription rate plan that has two charges with each charge generating an invoice item then I'll see the rate plan name appear (repeated) on each invoice item row, so it'll appear twice. So if we have a subscription name of 'Sub-01', a rate plan of 'RP-01' and two rate plan charges, 'RPC-01' and 'RPC-02' which then generate invoice items on INV-123 of $1 and $2 and then run a query of these columns for INV-123 you'll see something like the following:
Sub Name | RatePlan | ChargeName | InvNum | Amount |
Sub-01 | RP-01 | RPC-01 | INV-123 | $1 |
Sub-01 | RP-01 | RPC-02 | INV-123 | $2 |
WIth our reporting tool or with the API using ZOQL you can sum the amounts per invoice or per invoice per rate plan, for example to get:
Sub Name | RatePlan | ChargeName | InvNum | Amount |
Sub-01 | RP-01 | RPC-01 | INV-123 | $3 |
But is this info helpful? In our reporting tool you'd leverage our Summary report feature creating a report on the invoice item data source. If you take a look at the OOTB report, 'Gross billing by product over time' you can see an example where the report sums invoice amounts per product, but you could customize this to do a 'per rate plan' summary instead if you wished.
- Mark as New
- Bookmark
- Subscribe
- Permalink
- Email to a Friend
- Report Inappropriate Content
All of the data sources, including invoice item, report all the selected fields for all returned rows. So if I'm querying invoice items and inlcude the rate plan name in the results AND I have a subscription rate plan that has two charges with each charge generating an invoice item then I'll see the rate plan name appear (repeated) on each invoice item row, so it'll appear twice. So if we have a subscription name of 'Sub-01', a rate plan of 'RP-01' and two rate plan charges, 'RPC-01' and 'RPC-02' which then generate invoice items on INV-123 of $1 and $2 and then run a query of these columns for INV-123 you'll see something like the following:
Sub Name | RatePlan | ChargeName | InvNum | Amount |
Sub-01 | RP-01 | RPC-01 | INV-123 | $1 |
Sub-01 | RP-01 | RPC-02 | INV-123 | $2 |
WIth our reporting tool or with the API using ZOQL you can sum the amounts per invoice or per invoice per rate plan, for example to get:
Sub Name | RatePlan | ChargeName | InvNum | Amount |
Sub-01 | RP-01 | RPC-01 | INV-123 | $3 |
But is this info helpful? In our reporting tool you'd leverage our Summary report feature creating a report on the invoice item data source. If you take a look at the OOTB report, 'Gross billing by product over time' you can see an example where the report sums invoice amounts per product, but you could customize this to do a 'per rate plan' summary instead if you wished.
- Mark as New
- Bookmark
- Subscribe
- Permalink
- Email to a Friend
- Report Inappropriate Content
Re: HOW TO deduplicate Invoice Items what have multiple Rate Plans?
I managed to pull the right results by running my query Grouping the rows by Invoice Number. 🙂