Inserting total count of passed conditional expression to a column

Five Stars

Inserting total count of passed conditional expression to a column

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.


Accepted Solutions
Fifteen Stars TRF
Fifteen Stars

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

Based on the following data:

sample.png

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

tMap.png

Then, you have to aggregate the result:

tAggregate.png

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:

job.png

 


TRF
Fifteen Stars TRF
Fifteen Stars

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

@KarthikVenky, does this help?

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


TRF

All Replies
Community Manager

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

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

Fifteen Stars TRF
Fifteen Stars

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

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().

TRF
Five Stars

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

@rhall_2_0 @ 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

Five Stars

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

@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.

Community Manager

Re: Inserting total count of passed conditional expression to a 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.

Fifteen Stars TRF
Fifteen Stars

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

Based on the following data:

sample.png

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

tMap.png

Then, you have to aggregate the result:

tAggregate.png

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:

job.png

 


TRF
Fifteen Stars TRF
Fifteen Stars

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

@KarthikVenky, does this help?

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


TRF

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Put Massive Amounts of Data to Work

Learn how to make your data more available, reduce costs and cut your build time

Watch Now

How OTTO Utilizes Big Data to Deliver Personalized Experiences

Read about OTTO's experiences with Big Data and Personalized Experiences

Blog

Talend Integration with Databricks

Take a look at this video about Talend Integration with Databricks

Watch Now