Pick value based on condition

[joe86]

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

AppID	Venue	Contact	Offical	C1	C2	C3	C4	C5
1	abc	123	Coach	21/12/2018
2	asc	124	Coach		20/12/2018
3	zxc	125	DSO				19/12/2018
4	aqw	126	Vol					19/12/2018
5	qqw	127	Vol					20/12/2018
6	ret	128	DSO				20/12/2018
7	fgh	129	Coach			19/12/2018
8	gbm	130	Coach		18/12/20183

Table2:

Official	Certificate
Coach	C1
Coach	C2
Coach	C3
DSO	C4
Vol	C5

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.

[Anonymous · Accepted solution]

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.

job flow

output

Below are the individual component screenshots.

tmap expression of C1 same for all  columns)

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

!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 🙂