Mapping Codes to Field values based on timestamps

[smiling_cheetah]

Hello everyone, I'm trying to find a solution to the following problem and would appreciate any help

There are two table:

1) TransactionTable

Date	Login	Action
01.02.2017	Login1	A1
01.05.2017	Login1	A2
01.09.2017	Login1	A3
01.03.2017	Login2	A4
01.08.2017	Login2	A5

2) MappingTable

Login	Code	InclusionDate	ExclusionDate
Login1	Code1	01.01.2017	20.03.2017
Login1	Code2	21.03.2017	15.08.2017
Login1	Code3	16.08.2017	29.11.2017
Login2	Code100	01.01.2017	01.06.2017
Login2	Code101	02.06.2017	29.11.2017

I am trying to add Code field to the TransactionTable:

Date	Code	Login	Action
01.02.2017	Code1	Login1	A1
01.05.2017	Code2	Login1	A2
01.09.2017	Code3	Login1	A3
01.03.2017	Code100	Login2	A4
01.08.2017	Code101	Login3	A5

Given that a Code for certain Login can change arbitrary number of times.

I was thinking about creating a new table such as

Code	Date
Code1	01.01.2017
Code1	02.01.2017
Code1	03.01.2017
etc	etc

and than map using where exists statement on dates, but I guess there can be more elegant and less resource-dependent solutions.

Thanks in advance (and apologies if there already been such topic, I couldnt find any)

[marcus_sommer · Accepted solution]

I think mapping is no bad idea and would probably be the way which I would go with. For it you would need to expand your mapping table for a "real" date which is most commonly done per: IntervalMatch whereby I personally prefer the use f a while-loop (but the link explained the logic behind it very well). The result maybe look like:

Map:

mapping load Login & '|' date(InclusionDate + iterno() - 1), Code

from MapSource while InclusionDate + iterno() - 1 <= ExclusionDate;

and then you could use within loading the transact-data:

applymap('Map', Login & '|' Date, '#NV') as Code

- Marcus

[smiling_cheetah]

Thank you, Marcus!

Works as intended