Set date on condition

Six Stars

Set date on condition

Hello!

I have an excel input step where I am reading multiple excel files and loading it in a SQL table (tFileList --> tFileInputExcel). The structure of all files is same but data is different (Region1.xlsx, Region2.xlsx and so on). Now while loading I will have to assign a start date to projects with a condition. The condition is if the project is not present in the table then assign loading timestamp as start date else do not change the existing row. How can I do this?

 

Regards

PV

Forteen Stars

Re: Set date on condition

@priyadarshiniv ,can you share sample data and required output?

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Six Stars

Re: Set date on condition

@manodwhb 

Say for example: 

Initial load gets 2 projects. Lets say loading happens on 18.10.2019

Project numberPeriodTurnover
Project110.2019100000
Project210.2019200000

So the table looks like:

Project numberPeriodTurnoverStart date
Project110.201910000018.10.2019
Project210.2019200000

18.10.2019

 

Now in period 11.2019, a new projects gets added along with the old ones. Lets say loading happens on 18.11.2019

Project numberPeriodTurnover
Project111.2019500000
Project211.2019600000
Project 311.2019700000

Then the table should have following values:

Project numberPeriodTurnoverStart date
Project110.201910000018.10.2019
Project210.201920000018.10.2019
Project111.201950000018.10.2019
Project211.201960000018.10.2019
Project 311.201970000018.11.2019

 

Hope this explains the required condition.

 

Regards

Priya

Employee

Re: Set date on condition

Hi Priya,

 

    The first step will be to identify the unique records based on project number. This can be achieved by reading from the table with group by clause.

SELECT  project_name, start_date
from project_table
group by project_name, start_date

 You can store this data to either Hash components or temporary files.

 

In process next month data, you read the input data from file and do a left outer join with above lookup data based on project_name.

If the project name lookup is null, use the input start date (or populate current date) else use the date from lookup.

 

image.png

 

Warm Regards,
Nikhil Thampi

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

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

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog