How to compare string field with reference field in transform map?

[Sathwik1]

I have a field in Excel named "Schedule" it maps with "Schedule" in cmn_schedule_span table...

so here my issue is...

Suppose in Excel If I provide like "India Schedule" while transforming it is referring to "Schedule" which is a reference field due to this my transform map is not working...

so can anyone please explain how can we compare a string with reference field in transform map 

[Ankur Bawiskar]

Hi,

So it should be combination of Schedule + Start + End + Year

Regards
Ankur

[Sathwik1]

@Ankur Bawiskar There is no year column separately, start and end includes year too...please see the screenshot and also please once check my previous comment and help @Ankur Bawiskar 

[Ankur Bawiskar]

Hi,

then can you try to use this combination then

Schedule + Start Time + End Time

Regards
Ankur

[Sathwik1]

@Ankur Bawiskar already tried that...

if I keep Schedule + Start Time + End Time as coalsece field then if three matches then only it is gettting updated other wise it is inserting new record...which won't satisfies my condition...

because as I am working on calendar update..

Independence day  15-08-2020 00:00:00 15-08-2020  23:59:59 (as of now)

it needs to update as 

Independence day  15-08-2021 00:00:00 15-08-2021  23:59:59

in this scenarios if I use coalsece field as  Schedule + Start Time + End Time then it will creating new record...but I need to update in these scenarios

[Ankur Bawiskar]

Hi,

since there is change in the coalesce field value it will insert

So if you wish to change the value for the year I believe you will have to script it using transform map onBefore script and don't use any field map

1) query the target table with the schedule and find if the start date and the date from start time and same for end date are matching

2) if yes then update with the excel values

Regards
Ankur