Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
rittermd
Master

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

3 Replies
Anil_Babu_Samineni

Will you be able to share some sample data?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
rittermd
Master
Author

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.

Anil_Babu_Samineni

You mean this?

Dimension : Invoice#

Expression : Count(Suffix)

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful