Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
falko_thom
Contributor III
Contributor III

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

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.

5 Replies
Not applicable

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

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

falko_thom
Contributor III
Contributor III
Author

Hi,

The value in NEA_Datum determines the "real name" of OrgLevel3.

Thanx

falko_thom
Contributor III
Contributor III
Author

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