5 Replies Latest reply: Jan 30, 2014 4:52 AM by Falko Thom RSS

    need field-content to be replaced during script - how?

    Falko Thom

      Hello Community,

       

      I've got a table with incoming mail. This table also contains which department received the mail.

      Unfortunately during time the identifier (OrgLevel3) of the department always stays the same although its name or use changes.

       

      So I need to replace the value in OrgLevel3. So I created another table (Abteilungshistorie) with the identifier of the department, beginning and ending date and a field for the new name.

       

      Now I either want to replace the content in table 'NEA-Vorgänge' or use the field 'neuerName'.

       

      I already tried to look up a solution and found 'mapping' or 'intervalmatch', but none of these really help.

       

      I really appreciate if someone can help me.

      Thanx a lot.

        • Re: need field-content to be replaced during script - how?
          Srikanth P

          HI, How do you decide, you need use which field? based on Begindate & Enddate on today's date?

          • Re: need field-content to be replaced during script - how?
            Peter Cammaert

            I guess that once an OrgLevel3 value changes, it will have one or more successive definitions for neuerName. The only thing you need to add to table Abteilungshistorie is start records that have no start date, an end date one day less than the first change record, and a neuerName equal to the original OrgLevel3. Then you can use INTERVALMATCH to get a connection between the source and history table (never mind the synthetic keys), and start using neuerName in all cases to obtain the correct OrdLevel3 name for that specific point in time. Right?

             

            Just my 2cts.

             

            Peter

              • Re: need field-content to be replaced during script - how?
                Peter Cammaert

                OR,...

                 

                you could create a gigantic mapping table where the index is composed of Original OrgLevel3 & '-' & Date and the lookup value is the new name for that moment in time.

                 

                You only have to create entries for each row in Abteilungshistorie, because the default correct original value OrgLevel3 can be passed as a third parameter to applymap() so that whenever a date isn't found, the original name will be returned.

                 

                Use a master calendar-like technique to translate Abteilungshistorie into a mapping table, e.g.

                 

                MAPPING

                LOAD OrgLevel3 & '-' & date(BeginDate+IterNo()-1) as Index,

                         neuerName as LookupVal

                RESIDENT Abteilungshistorie

                WHILE BeginDate+IterNo()-1 <= EndDate;

                 

                Good luck,

                 

                Peter

                • Re: need field-content to be replaced during script - how?
                  Falko Thom

                  Thank you so far Peter,

                   

                  I would like to take solution A (no huge mapping table).

                  In my first posting I forgot to mention that the new name for OrgLevel3 depends on NEA_Datum from table NEA_Vorgänge. Is it still possible to find a smart solution?

                  It is possible that in future the Name of the department will change so I would have to add another period for an existing OrgLevel3. On the other hand there will be departments which never ever will change their names. Do I have to put up a line with these Names where the values for 'OrgLevel3' and 'neuerName' are the same?

                   

                  Greetings, Falko