Count distinct rows

[manu0521]

Hi ,

I have a transaction file that i load in ti , user is asking for a requirement to count the number of distinct invoices by customer and report as a measure.

I have done this by loading in sql and then doing a distint count by invoice for each customer and then loaded that to tm1.

But is there a way to perform distinct count directly in tm1 ti process.

Thanks,

[ascheevel]

Would I be correct in assuming an invoice could be represented on multiple rows of your transaction file so a simple row count won't work?

You could populate a temporary dim in metadata of your import process with the invoice numbers and then do a DIMSIZ in the epilog before deleting the dimension. You could also make it a permanent dim and group the invoices by time period and then ELCOMPN the specific time period to get invoice count.

[Emixam]

I think Ascheevel's solution is the best but if you want to do everything within a TI, you should do something like this :

Code: Select all

#=====================================
# Prolog
#=====================================

sStringOfInvoices = ''
nDistinctInvoices = 0;

#=====================================
# Data
#=====================================

IF( SCAN( TRIM( '_' | UPPER( vInvoice ) | '_' ), UPPER( sStringOfInvoices ) ) = 0 );
	nDistinctInvoices = nDistinctInvoices + 1;
	sStringOfInvoices = sStringOfInvoices | '_' | vInvoice | '_' ;	
ENDIF;

nDistinctInvoices will hold the number of distinct invoices.

PS: A string variable can be no more than 8000 characters long. Depending on the number of invoices you have in your file, you will have to add a IF Statement using the LONG function.