Set date on condition

[Anonymous]

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

[manodwhb]

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

[Anonymous]

@manodwhb 

Say for example: 

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

Project number	Period	Turnover
Project1	10.2019	100000
Project2	10.2019	200000

So the table looks like:

Project number	Period	Turnover	Start date
Project1	10.2019	100000	18.10.2019
Project2	10.2019	200000	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 number	Period	Turnover
Project1	11.2019	500000
Project2	11.2019	600000
Project 3	11.2019	700000

Then the table should have following values:

Project number	Period	Turnover	Start date
Project1	10.2019	100000	18.10.2019
Project2	10.2019	200000	18.10.2019
Project1	11.2019	500000	18.10.2019
Project2	11.2019	600000	18.10.2019
Project 3	11.2019	700000	18.11.2019

Hope this explains the required condition.

Regards

Priya

[Anonymous]

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.

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