Seven Stars

Flag not updating based on condition

Hi all,

My requirement is to load data from excel to oracle table with below conditions

Table columns are 

cust_no,Name,Item,Orders,Location,Flag

cust_001, John, Watch,3, Mumbai, I-------------------->flag should change

1.If record already exists in table then FLAG column should change from "I" to "U"

Expected output:

cust_no,Name,Item,Orders,Location,Flag

cust_001, John, Watch,3, Mumbai, U---------------------->Already existing record on table 

cust_002, Mathew, Bag, 3, Bangalore, I

cust_001, John, Watch,3, Mumbai, I-------------------->record from excel should insert like this

My job design looks like below screenshot

UpFlag.PNGJOb design.PNGmap.PNG

 

The problem is new records from excel inserting with flag "I" but  existing record flag is not Insert as the new record it just update the existing record Flag

1.InsertRec--->output-->action on table --->Insert

2.Update -->output-->action on table -->Tried with Insert or update  ,update or insert,update but it's not  inserting as new record

When I tried with Insert as action on table it is inserting the record which is already in table is as below

cust_no,Name,Item,Orders,Location,Flag

cust_001, John, Watch,3, Mumbai, U

 

when I work with start_date,end_date using SCD component it's giving me exactly what I'm looking for.

 

Hope you understand the problem.

 

As soon as possible please help me to resolve this!

Thanks in advance

1 ACCEPTED SOLUTION

Accepted Solutions
Community Manager

Re: Flag not updating based on condition

2.for the record which flag changed from "I" to "U" start_date becomes "null" end_date as "sysdate"
->I think the job need two changes:
1. Add a filter condition in the lookup table to query only the "I" records, eg:
"select * from tableName where flat='I'"
the start_date of output table on tMap1 maps the start_date column of the lookup table.
2. On the schema of tMysqlouput for update, set the two columns as key: cust_no and start_date


----------------------------------------------------------
Talend | Data Agility for Modern Business
19 REPLIES
Twelve Stars TRF
Twelve Stars

Re: Flag not updating based on condition

Hi Sara,

 

How are setted the "Catch lookup inner join reject" option on both output flows?

It should be "True" for Insertrec flow, and "False" to Update flow.

Also, you can hard code the FLAG value for both flows, "I" for Insertrec and "U" for Update.


TRF
Seven Stars

Re: Flag not updating based on condition

Hi @TRF,

 

Like you said already I kept the same, please have a look at the below screenshot

TRF.PNG

 

am I doing any mistake in the variables "updateFlag" and "InsertFlag"?

 

Community Manager

Re: Flag not updating based on condition

Hi 
If I understand your request well, you want to insert the source record also into db even though it already exists. There is a design issue in your job, the existing records always go to the update output on tMap and are updated in db.

Regards
Shong

----------------------------------------------------------
Talend | Data Agility for Modern Business
Seven Stars

Re: Flag not updating based on condition

Hi  @TRF

I just replaced variable and hard code as "i"  and "U"

and I got below output

output.PNG

 

I kept Action on table for both output is as "INSERT" only

 

 

Twelve Stars TRF
Twelve Stars

Re: Flag not updating based on condition

How is the "Catch lookup inner join" option for Insertrec flow?

Regarding the variable "InsertFlag", as soon as the record is not found in db because it is a new one, you should simply hard code the value ("I") in the output flow like this:

 

Capture.PNG

Also think you can do the same for Update flow with the value "U", don't you?


TRF
Seven Stars

Re: Flag not updating based on condition

Hi @shong

 

Ya you got my requirement, but I don't know  what mistake I did 

Seven Stars

Re: Flag not updating based on condition

Hi @TRF,

Catch lookup inner join reject for "Insertrec" is true only

Seven Stars

Re: Flag not updating based on condition

Hi,

 

I hope I got output like I shown the above screenshot.

I just want to make sure whether it works fine by adding date column.

Something like 

start_date and End_date

 

Will work on it and get back to you if I face any problem

Twelve Stars TRF
Twelve Stars

Re: Flag not updating based on condition

I've just read again you original post, so I understand every record from Excel file must be inserted into the db with a value of "I" if it doesn't exists yet in the db, and a value of "U" if it exists.

IMHO, "Action on data" should always be set to "Insert" and you just need 1 output flow for both new and exisiting records.

For this, use an "Left Outer Join" in tMap, then decide the value of the flag field depending on the join result (field will be null for new records, so you just have to replace the value by "I" in this case):

Capture.PNG

I've not tested but should works.


TRF
Seven Stars

Re: Flag not updating based on condition

Hi @TRF,

 

Here is my expected attached for your perusal, please have a look at it.

tmap.PNGoutput.PNG

 

Expected Output:

 

expected_op.png

Twelve Stars TRF
Twelve Stars

Re: Flag not updating based on condition

You want hour after the date?

Try to use TalendDate.formatDate("MM/dd/yyyy hh:mm:ss",TalendDate.getCurrentDate())


TRF
Seven Stars

Re: Flag not updating based on condition

Hi @TRF,

 

NO, I don't want hour you can notice data In which 5 records already exists in a table with the start_date as 5/24/2017 and flag as "I" .(ie.for

cust_001,cust_002,cust_003,cust_004,cust_005)

In Excel Records presents are (cust_006,cust_007,cust_008,cust_009,cust_001)

so here cust_001 already exists in the table with start_date 5/24/2017.

what I expect is those records which are already in the table should update its flag from "I" into "U" and start_date 

1.update its flag from "I" into "U" and

2.start_date is same as the date in the table ie(5/24/2017) and end_date becomes the system date.

3.record from Excel should insert as new record 

that is (cust_001, john, HandBag, 2, Chennai, I, current date.

Seven Stars

Re: Flag not updating based on condition

Hi everyone,

 

Can anyone help me to get the solution for my scenario?

 

Hope you understand the scenario!

Community Manager

Re: Flag not updating based on condition

Hi Sara
I think there is a design issue, should be:
tFileInputEexcel--main--tMap1(inner join)---update..tMysqlOutput (for upate)
                                        |
                                    lookup
                                        |
                              tMysqlOutput
|
onsubjobok
|
tFileInputExcel--main--tMap2----tMysqlOuput(insert)

on tMap1: do an inner join to get only the records onlywhich already exists in target db, these records will be updated.
Set the flag column to "U"
Set the end date column as TalendDate.getCurrentDate()

on tMap2: just add two new columns in the output table
flag: set it to "I"
start date: set it to TalendDate.getCurrentDate()

Let me know if it works.

Regards
Shong

----------------------------------------------------------
Talend | Data Agility for Modern Business
Seven Stars

Re: Flag not updating based on condition

Hi,

Thanks your quick response @shong

 

I have followed your job flow as expected I got an output for flag column coming to date 

1.for new rec its start_date as 'sys date' works fine.

2.for the record which flag changed from "I" to "U" start_date becomes "null" end_date as "sysdate"

my requirement is that start_date should change based on the record coming data. 

I hope you can understand the logic which is missing here  

have a look at below screenshot

I would like to achieve that

Exp_scd2.png

 

Community Manager

Re: Flag not updating based on condition

2.for the record which flag changed from "I" to "U" start_date becomes "null" end_date as "sysdate"
->I think the job need two changes:
1. Add a filter condition in the lookup table to query only the "I" records, eg:
"select * from tableName where flat='I'"
the start_date of output table on tMap1 maps the start_date column of the lookup table.
2. On the schema of tMysqlouput for update, set the two columns as key: cust_no and start_date


----------------------------------------------------------
Talend | Data Agility for Modern Business
Seven Stars

Re: Flag not updating based on condition

Hi @shong

 

Thanks a million

Finally, I got my expected result.


result.PNG

 

If you don't mind can you explain me in short what is the concept blind this, it would be helpful for me!

 

Community Manager

Re: Flag not updating based on condition

Glad to see it helps you! Smiley Wink Smiley Wink
Hard to say what is the concept, It's just my experience.
----------------------------------------------------------
Talend | Data Agility for Modern Business
Seven Stars

Re: Flag not updating based on condition

You are great! Smiley Happy

 

Thanks a lot