Pick value based on condition

Seven Stars

Pick value based on condition

Hi All,

I am working on a scenario. I have two tables.

1. Table1 has all the final output data.

2. Table2 has list of officialType & Certificates to be completed for those officials.

 

My Scenario:

I have to load only those records whose certificates are going to expire (in next 7 days) based on the OfficialType (in Table2).

If the official is an Coach, then the certificates (to be expired in next 7 days) assigned to him (either C1, C2, C3) should only flow to final Table1. Same condition applies to other OfficialType also.

Table:1

AppIDVenueContactOfficalC1C2C3C4C5
1abc123Coach21/12/2018    
2asc124Coach 20/12/2018   
3zxc125DSO   19/12/2018 
4aqw126Vol    19/12/2018
5qqw127Vol    20/12/2018
6ret128DSO   20/12/2018 
7fgh129Coach  19/12/2018  
8gbm130Coach 18/12/20183   

Table2:

OfficialCertificate
CoachC1
CoachC2
CoachC3
DSOC4
VolC5

 

If C1 date (from incoming source) is going to expire within next 7 days, then i have to populate only that date in Table1 (as shown in AppID=1 in Table1). Since other two certificates (C2, C3) for Coach does not expire in next 7 days, they will not flow into Table1 for AppID=1. Same condition for other two OfficialType also.

 

can some one please share me your thoughts.

 

Note: The list of certificates for each OfficialType can vary based on requirement.


Accepted Solutions
Employee

Re: Pick value based on condition

Hi,

 

     I was able to create the flow but I will have to say that your current model is not scalable as every new certificate addition means change in the code.

 

   Having said that, below is the flow.

image.pngjob flow

 

image.pngoutput

 

Below are the individual component screenshots.

image.png

 

image.png

 

image.png

 

tmap expression of C1 same for all  columns)

TalendDate.diffDate(row2.C1,TalendDate.getCurrentDate(),"dd",true) <=7? row2.C1:null 

image.pngimage.pngimage.png

 

!Relational.ISNULL(out1.C1) &&  row5.certificate.contains("C1")? out1.C1: null 

You will get the desired output from tmap.

 

Warm Regards,
Nikhil Thampi

Please appreciate our members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)

All Replies
Employee

Re: Pick value based on condition

@joe86

 

   I am slightly confused here. What is the the input data set? Is it Table 1?

 

   If yes, could you please show be the before and after status of this table? If no, could you please share the input source data?

 

Warm Regards,

 

Nikhil Thampi


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
Seven Stars

Re: Pick value based on condition

Input data set will be an join of tables from MySQL & MongoDB. The Certificate DATE details are coming from MongoDB table. Below is the screenshot of my existing one.

 

Capture.JPG

 

1: SQL Server where i get al my contact, AppID, EmpID, OfficialType details.

2: Passing EmployeeID one by one to tFlowToIterate.

3. For Each EmployeeID, I am getting the Certificate DATE

As of now, I am pulling records whose Certificate DATE is to be expired in next 7 days in tMap_2.

The Table1 in my query was the "O/P" menioned in the last flow of the screenshot.

 

So, now my new requirement is, pick only those certificates which is going to expire for each OfficalType.

Please let me know if you have anymore doubts.

 

Employee

Re: Pick value based on condition

Hi,

 

    I am still slightly confused with your requirement. I am assuming that you are looking for the earliest date for each Official type for each app id. Also your plan is to use the below table as the input.

 

AppIDVenueContactOfficalC1C2C3C4C5
1abc123Coach21/12/2018    
2asc124Coach 20/12/2018   
3zxc125DSO   19/12/2018 
4aqw126Vol    19/12/2018
5qqw127Vol    20/12/2018
6ret128DSO   20/12/2018 
7fgh129Coach  19/12/2018  
8gbm130Coach 18/12/20183   

 

In this case, the first stage will be to merge the values of C1, C2, C3, C4 and C5 to a single column called C. You can do it by tmap using concatenation operator (+) after converting the values to String. The new values will be as shown below.

 

AppIDVenueContactOfficalC
1abc123Coach21/12/2018;;;;
2asc124Coach ;20/12/2018;;;
3zxc125DSO ;;;19/12/2018;
4aqw126Vol ;;;;19/12/2018
5qqw127Vol ;;;;20/12/2018
6ret128DSO ;;;20/12/2018;
7fgh129Coach ;;19/12/2018;;
8gbm130Coach ;18/12/2018;;;

 

Now, use the tnormalize component to bring the values to same column and at the same time removing semi columns. If there are multiple values, there will be multiple entries for each record for each value in C for that row. Once its also complete, remove the records with C values as null and take the minimum value for each official type by passing the resultset to a taggregaterow component.

 

If the answer has helped you, could you please mark the topic as resolved? Kudos are also welcome :-)

 

Warm Regards,

 

Nikhil Thampi


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
Seven Stars

Re: Pick value based on condition

@nikhilthampi

Thanks a lot for ur research and help. But the solution u have provided does not suit my need.

I have tried to explain it below:

Basically, i want to pull out the certificate DATE that is going to expire in next 7 days. Each employee has 3 roles - Coach, DSO & Volunteer. For an employee to be a Coach, he should complete the certificate C1, C2, C3 and if any of these certificate is going to expire in next 7 days then only that Certificate details (date of expiry) should be populated in target table. So as per my Table2, Coach certificates are C1,C2,C3; DSO certificate is C4 and Volunteer certificate is C5.

So in my below target (final) table,

AppIDVenueContactOfficialC1C2C3C4C5
1abc123Coach21/12/2018    
2asc124Coach 20/12/2018   
3zxc125DSO   19/12/2018 
4aqw126Vol    19/12/2018
5qqw127Vol    20/12/2018
6ret128DSO   20/12/2018 
7fgh129Coach  19/12/2018  
8gbm130Coach 18/12/20183   


1. for AppID=1, For OfficialType = Coach, Only Certificate C1 is going to expire in next 7 days. C2 & C3 expiry date is > CurrentDay+7days
2. for AppID=2, For OfficialType = Coach, Only Certificate C2 is going to expire in next 7 days. C1 & C3 expiry date is > CurrentDay+7days
3. for AppID=7, For OfficialType = Coach, Only Certificate C3 is going to expire in next 7 days. C1 & C2 expiry date is > CurrentDay+7days
4. for AppID=3, For OfficialType = DSO, Certificate C4 is going to expire in next 7 days.

 

The point to be noted here is, the list of certificates against each of the official may be changed based on requirement. For instance after one month, the Coach should complete certificates C1 & C2, DSO should have C3 & C4 and Volunteer should have C5 & C6

The source:
As mentioned before, the Certificate details is fetched from MongoDB and Employee OfficialType is pulled from SQL Server. These two DB are joined based on the EmployeeID which is common in both these DBs.

 

I believe, i have explained it clear here. please let me know if you have any doubts. Smiley Happy

Thanks a lot again for ur time and effort..

Employee

Re: Pick value based on condition

@joe86

 

Lets go back to basics and start with a clean slate. Now I understood that the above table is your desired output.

 

Could you please share the input schema for each input source? I know that i MongoDB,you are fetching only specific columns instead of full collection.  Please share the columns we are getting from MongoDB also so that I will know the schema fro each source and then we can plan how to link them all.

 

Warm Regards,
Nikhil Thampi

Please appreciate our members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

 


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
Seven Stars

Re: Pick value based on condition

@nikhilthampi

PFB the input Columns fetched from both the DB:

SQl ServerMongo
AppIDEmpID
VenueC1 Date
ContactC2 Date
OfficalC3 Date
EmpIDC4 Date
 C5 Date

 

EmpID is the common fields for joining these two DB.

Seven Stars

Re: Pick value based on condition

@nikhilthampi

OfficialCertificate
CoachC1
CoachC2
CoachC3
DSOC4
Vol

C5

This is another table which has only the OfficialType and the list of certificates for those officials. This table is just to get the certificate details based on which the output data will be populated.

Employee

Re: Pick value based on condition

@joe86

 

Can you add the sample data for SQL Server and the MongoDB also for the same output condition?

 

Warm Regards,
Nikhil Thampi

Please appreciate our members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

 


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
Seven Stars

Re: Pick value based on condition

@nikhilthampi

SQlServer:

AppIDVenueContactOfficalEmpID
1abc123Coach123
2asc124Coach456
3zxc125DSO786
4aqw126Vol147
5qqw127Vol258
6ret128DSO369
7fgh129Coach159
8gbm130Coach357

 MongoDB:

EmpIDC1C2C3C4C5
12321/12/20185/1/201910/1/20195/1/201910/1/2019
4565/1/201920/12/201810/1/20195/1/201910/1/2019
7865/1/201920/12/201810/1/201919/12/20185/1/2019
1475/1/201920/12/201810/1/201910/1/201919/12/2018
2585/1/201920/12/201810/1/201910/1/201920/12/2018
3695/1/201920/12/201810/1/201920/12/20185/1/2019
1595/1/201910/1/201919/12/201810/1/20195/1/2019
3575/1/201918/12/201810/1/201910/1/20195/1/2019

 

My required Output:

AppIDVenueContactOfficalC1C2C3C4C5
1abc123Coach21/12/2018    
2asc124Coach 20/12/2018   
3zxc125DSO   19/12/2018 
4aqw126Vol    19/12/2018
5qqw127Vol    20/12/2018
6ret128DSO   20/12/2018 
7fgh129Coach  19/12/2018  
8gbm130Coach 18/12/20183   
Employee

Re: Pick value based on condition

Hi,

 

     I was able to create the flow but I will have to say that your current model is not scalable as every new certificate addition means change in the code.

 

   Having said that, below is the flow.

image.pngjob flow

 

image.pngoutput

 

Below are the individual component screenshots.

image.png

 

image.png

 

image.png

 

tmap expression of C1 same for all  columns)

TalendDate.diffDate(row2.C1,TalendDate.getCurrentDate(),"dd",true) <=7? row2.C1:null 

image.pngimage.pngimage.png

 

!Relational.ISNULL(out1.C1) &&  row5.certificate.contains("C1")? out1.C1: null 

You will get the desired output from tmap.

 

Warm Regards,
Nikhil Thampi

Please appreciate our members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)

Cloud Free Trial

Try Talend Cloud free for 30 days.

Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.