

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Need to get count of invoice suffixes
I have invoice data. There is an invoice number and that invoice can have multiple lines. Versions of the invoice contain a value in the suffix (a,b,etc.)
So what I want to do in the load script is count how many different suffixes an invoice has. The problem I am running into is that that each suffix version has the same number of lines as the original. So if the invoice has 5 lines then I get 5 lines for no suffix(original) and five lines for suffix a and five lines for suffix b, and so on.
I have an If statement that is not doing what I want. In the above example my count ends up as 5 when it should be 3.
If (InvoiceNumberSuffix = Peek(InvoiceNumberSuffix), Peek('Count')+1,1) as count
Hopefully this made some sense. Thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Will you be able to share some sample data?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Here is an inline table example
Load * Inline [
Inv_Suffix,Invoice#,Suffix
1,1,
1A,1,A
1B,1,B
1C,1,C
2,2
As you can see I have 4 rows for Invoice#=1 each with a different Suffix. So what I want to end up with is a table that has the invoice #, and the count of unique suffixes. In this example the new table would be
1,4
2,1
To make it a little more complicated each inv_suffix can have multiple rows in the original table. One for each line on that invoice. Each has a different charge code and amount.
Again I want to end up with a table that contains the invoice number and the count of unique suffixes.
Hope this helps.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You mean this?
Dimension : Invoice#
Expression : Count(Suffix)
