topic Re: Inserting total count of passed conditional expression to a column in Talend Studio

Inserting total count of passed conditional expression to a column

Anonymous — Sat, 16 Nov 2024 04:42:35 GMT

Dear All,

I am having a Source table containing Current_Month,Date of Joining(DOJ) and Date of Separation(DOS).

And in Destination table I have head count, where i need to insert head count of employee based on below condition,

COUNT(((TalendDate.formatDate("MMM-yy", row1.DOJ)<=Current_Month) &&
(TalendDate.formatDate("MMM-yy", row1.DOS)>=Current_Month))?1:0)

I have used above expression on head count column in destination table. But it is not working. Could you please assist me, how to get solved.

Thank you in advance.

Re: Inserting total count of passed conditional expression to a column

Anonymous — Wed, 11 Sep 2019 12:19:25 GMT

Where are you trying to use this expression? Is it being used in SQL or Java?

Re: Inserting total count of passed conditional expression to a column

TRF — Wed, 11 Sep 2019 12:40:15 GMT

You cannot compare dates based on MMM as "OCT" will be considered as less than "SEP" but in fact it's greater (10 > 09).
Instead you should use TalendDate.compareDate().

Re: Inserting total count of passed conditional expression to a column

Anonymous — Wed, 11 Sep 2019 13:14:47 GMT

@rhall @ I am trying to use it in expression builder function. If so its possible in java or sql. Could please let me know hownit can be approached

Re: Inserting total count of passed conditional expression to a column

Anonymous — Wed, 11 Sep 2019 13:19:41 GMT

@TRF yea understood. I can use TalendDate.compareDate() but later part of which i need to count number of passed condition. And insert that value in all rows of that column.

Re: Inserting total count of passed conditional expression to a column

Anonymous — Wed, 11 Sep 2019 13:33:47 GMT

"Count" is not a Java method I am aware of, but it is a SQL function. If you are trying to use this in a database component, then you need to do this in a different way to if you are doing this in a tMap or tJava, for example. By the way, given the logic you have here with the 0 : 1 I assume that you want to use something like "Sum" rather than "Count" if this is meant to be run by your database.

Re: Inserting total count of passed conditional expression to a column

TRF — Wed, 11 Sep 2019 15:27:45 GMT

Based on the following data:

you can use the following tMap to determine for each row if it should be set to 0 or 1:

Then, you have to aggregate the result:

Here is the result:

|=-----------+----=|
|CurrentMonth|Count|
|=-----------+----=|
|2019-09     |1    |
|2019-10     |1    |
'------------+-----'

Is that what you expect?

Else, give more details (where sample data and expected result).

Here is the whole job:

Re: Inserting total count of passed conditional expression to a column

TRF — Thu, 12 Sep 2019 09:19:23 GMT

@KathikVenky, does this help?

If so, thank's to mark your case as solved.