Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Contributor

Mapping Codes to Field values based on timestamps

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

There are two table:

1) TransactionTable

DateLoginAction
01.02.2017Login1A1
01.05.2017Login1A2
01.09.2017Login1A3
01.03.2017Login2A4
01.08.2017Login2A5

2) MappingTable

LoginCodeInclusionDateExclusionDate
Login1Code101.01.201720.03.2017
Login1Code221.03.201715.08.2017
Login1Code316.08.201729.11.2017
Login2Code10001.01.201701.06.2017
Login2Code10102.06.201729.11.2017

I am trying to add Code field to the TransactionTable:

DateCodeLoginAction
01.02.2017Code1Login1A1
01.05.2017Code2Login1A2
01.09.2017Code3Login1A3
01.03.2017Code100Login2A4
01.08.2017Code101Login3A5

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

I was thinking about creating a new table such as

CodeDate
Code101.01.2017
Code102.01.2017
Code103.01.2017
etcetc

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)

Tags (2)
1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Re: Mapping Codes to Field values based on timestamps

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

View solution in original post

2 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Mapping Codes to Field values based on timestamps

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

View solution in original post

Highlighted
Contributor

Re: Mapping Codes to Field values based on timestamps

Thank you, Marcus!

Works as intended