topic Re: row count from two files in Talend Studio

row count from two files

shalinim1 — Sat, 16 Nov 2024 01:23:39 GMT

Hi,

In my job, I am getting the input from database (MySQL) table. I want to get the row count of two input tables and want to compare the count. If the count id not equal, then the mail has to be triggered with the missing values as subject. Could anyone help me with the procedure?

Re: row count from two files

Anonymous — Thu, 24 Sep 2020 09:41:45 GMT

Use a tMysqlInput to execute a count query, and store the count to context variable for comparison later, for example:

tMysqlinput1--tJavaRow1

|onsubjobok

tMysqlinput2--tJavaRow2

|onsubjobok

tJava--runIf--tSendmail

on tJavaRow1:

context.count1=input_row.count;

on tJavaRow2:

context.count2=input_row.count;

on tJava:

if(context.count1==context.count2){

context.sendMail=false;

}else{

context.sendMail=true;

}

conext.count1, context count2 are context variables, int type.

context.sendMail, context variable, boolean type.

Set the condition of runif as: context.sendMail

Hope it helps you!

Regards

Shong

Re: row count from two files

manodwhb — Thu, 24 Sep 2020 09:44:31 GMT

@Shalini M , you take the two count queries and use in tDBInputs and the connect to tmap and then do inner join and take the innerjoin rejects to output in tMap and connect to tSendemail compontn to trigger email.

Thanks,

Manohar

Re: row count from two files

cadap — Wed, 30 Sep 2020 20:32:38 GMT

do you really need the count or only the differing id values?

if you need the differing values, you can perform this with a single sql query.

In MSSQL one would do this with the EXCEPT keyword.

But in MySQL you have to use Joins or Subselects.

This page explains it well:

http://www.geeksengine.com/database/multiple-table-select/minus-except.php

Re: row count from two files

shalinim1 — Thu, 01 Oct 2020 05:23:43 GMT

Thank you will check on it. I need the count of missing values and that count should be included as Subject in the mail we are triggering!

Re: row count from two files

cadap — Thu, 01 Oct 2020 14:08:22 GMT

For the count of the missing values, just get the missing values with the sql query and put the result to a count(*).

This can all be done in one query.

For two tables, table_1 and table_2, finding all missing values in table_2 with an arbitrary ID and count them:

SELECT count(t1.ID) FROM table_1 AS t1

LEFT JOIN table_2 AS t2 ON

t1.ID=t2.ID

WHERE t2.ID IS NULL;

If you want to find all values missing in table_1, just interchange the table names in the query.

Re: row count from two files

shalinim1 — Sat, 03 Oct 2020 14:23:28 GMT

getting row count of a table is okay. how to make it as the subject of mail? When I tried with a java code. it is sending mail with the entire code as subject.

Re: row count from two files

DataTeam1 — Mon, 05 Oct 2020 19:41:04 GMT

@Shalini M you need to use your context value in the subject field

Re: row count from two files

shalinim1 — Tue, 06 Oct 2020 03:49:43 GMT

Thank you. Will check on it.