Page 1 of 1
Count distinct rows
Posted: Tue Nov 19, 2019 9:45 pm
by 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,
Re: Count distinct rows
Posted: Tue Nov 19, 2019 10:35 pm
by 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.
Re: Count distinct rows
Posted: Thu Nov 21, 2019 2:29 pm
by 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.