[resolved] Measuring statistics inside a job

[dvorakstephen11]

Hi there,

I'm creating an automated job that will process an input file, place the output in a predetermined directory, and send an email to the department that submitted the input file.

I want the email to contain several statistics, such as:
[list=*]

• the number of rows that were filtered out of the input file based upon a match in a table. I had planned to perform a count in a MySQLInput to get that number.

• the number of non-null values in two columns (the number of non-null values in each column would be a separate statistic)

I know this is a rather vague requests, but does anyone have any ideas of how I could capture these numbers and enter them into the automated email? I was assuming I would generate global variables that would capture those values during the job, and then I could call those variables in the email. 

Is there a way to capture the result of the MySQL COUNT function and place it inside a global variable, or should I take a different approach to record the number of filtered rows? 

Secondly, I was assuming I could use tJava or tJavaFlex with a globalMap.put to write a java code that would return the number of non-null values in two particular columns ("LenderFlag" and "CorrFlag"). I have precious little knowledge of java, so I'm not sure if such a code exists. Is there a better way to capture that number?

Let me know if I need to provide pictures of my job or provide more information about my goals and process needs. Thanks!

[Anonymous]

Hi
For the first number statistic , you can use a tMysqlInput to get the number and then link it to a tJavaRow, put the value to a global variable on tJavaRow, for example:
tMysqlInput--main--tJavaRow
on tJavaRow:
globalMap.put("yourKey1", input_row.countNumber)
//countNumber is the column name defined on tMysqlInput

For the second number statistic, read the file and link to a tJavaFlex,
in the begin part, define the two variable with int type. 

int c1=0;
int c2=0;

in the main part, increase the count number if the column value is not null, eg:

if(row1. LenderFlag!=null){
c1=c1+1;
}
if(row1. CorrFlag!=null){
c2=c2+1;
}

in the end part, store the count number to global variables. 

globalMap.put("yourKey2", c1);
globalMap.put("yourKey3", c2);

Finally, you can call those variables in the email.


Best regards
Shong

[dvorakstephen11]

Hey shong,
Thanks so much! That formula is just what I need. I am, however, running into one other problem, and I wonder if you might have an obvious solution. 
Below is the code in my tJavaFlex component:



As you can see, I'm trying to count the number of rows that have a value of "APPROVED" or "APPROVED - New Contact" in the LenderFlag column. 

Here is an image of the values in the LenderFlag column.





Here, also, is an image of my tMap component, which shows the identification for the column I'm attempting to count upon. 




The global variable I'm creating off of my CorrFlag count works perfectly. There are eight "CORR" values in the CorrFlag column of the file, and it returns a count of 8 in the e-mail. For some reason, my LenderFlag count returns a count of zero.

I assumed my string was incorrect, but I double and triple checked it and I can't see how I am using an incorrect string. Is there something else I'm doing incorrectly that is causing my count to be incorrect? 

Thanks again for your help!

[dvorakstephen11 · Accepted solution]

I figured out the problem with my LenderFlag count. I didn't realize that you had to use a !str.equals() syntax to match on strings, as opposed to the != that works for integers. All problems solved.