Solved: Pick value based on condition - Qlik Community

joe86 · ‎2018-12-14

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 · ‎2018-12-17

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 🙂

View solution in original post

Anonymous · ‎2018-12-14

@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

joe86 · ‎2018-12-14

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.

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.

Anonymous · ‎2018-12-16

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.

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

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.

AppID	Venue	Contact	Offical	C
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/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

joe86 · ‎2018-12-16

@nthampi

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,

AppID	Venue	Contact	Official	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

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.

Thanks a lot again for ur time and effort..

Anonymous · ‎2018-12-17

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

joe86 · ‎2018-12-17

@nthampi

PFB the input Columns fetched from both the DB:

SQl Server	Mongo
AppID	EmpID
Venue	C1 Date
Contact	C2 Date
Offical	C3 Date
EmpID	C4 Date
	C5 Date

EmpID is the common fields for joining these two DB.

joe86 · ‎2018-12-17

@nthampi

Official	Certificate
Coach	C1
Coach	C2
Coach	C3
DSO	C4
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.

Anonymous · ‎2018-12-17

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

joe86 · ‎2018-12-17

@nthampi

SQlServer:

AppID	Venue	Contact	Offical	EmpID
1	abc	123	Coach	123
2	asc	124	Coach	456
3	zxc	125	DSO	786
4	aqw	126	Vol	147
5	qqw	127	Vol	258
6	ret	128	DSO	369
7	fgh	129	Coach	159
8	gbm	130	Coach	357

MongoDB:

EmpID	C1	C2	C3	C4	C5
123	21/12/2018	5/1/2019	10/1/2019	5/1/2019	10/1/2019
456	5/1/2019	20/12/2018	10/1/2019	5/1/2019	10/1/2019
786	5/1/2019	20/12/2018	10/1/2019	19/12/2018	5/1/2019
147	5/1/2019	20/12/2018	10/1/2019	10/1/2019	19/12/2018
258	5/1/2019	20/12/2018	10/1/2019	10/1/2019	20/12/2018
369	5/1/2019	20/12/2018	10/1/2019	20/12/2018	5/1/2019
159	5/1/2019	10/1/2019	19/12/2018	10/1/2019	5/1/2019
357	5/1/2019	18/12/2018	10/1/2019	10/1/2019	5/1/2019

My required Output:

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

Pick value based on condition

Talend Data Integration

v7.x