Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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:
Where are you trying to use this expression? Is it being used in SQL or Java?
@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
@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.
"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.
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:
@KathikVenky, does this help?
If so, thank's to mark your case as solved.