How to map a field with a date condition without date ranges?

[t_guet01]

Hey guys,

I am trying to find out how to map a sales rep to transaction data. The sales reps in my data change their responsibility (product-wise) every other year. I see the following (exemplary) data:

Transactions:

TransactionDate	ProductGroupID	Desired Result Column: SalesRep
01.03.2020	123	ABC
05.03.2020	456	DEF
13.03.2020	123	DEF
15.03.2020	456	ABC

SalesRep Responsibilities:

SalesRep	ResponsibleProductGroupID	ResponsibleTime
ABC	123	01.01.2019
DEF	456	23.04.2019
ABC	456	11.03.2020
DEF	123	12.03.2020

As you see, the responsible SalesRep for the first transaction is "ABC". However, in March 2020, the responsibility for ProductGroup 123 changed and now SalesRep DEF ist responsible. Thus, the mapped SalesRep in row 3 is "DEF".

I want to create a column with "SalesRep" in the transaction data, sourcing from the "SalesRep" table. My SalesRep data only give a start date, however no end date. I would apply intervalmatch, but since no end date is given, I am kind of thinking of a conditional vlookup: using the "ProductGroupID" as search column, lookup the most recent (max date) sales responsibility under the condition that the ResponsibleTime is smaller than the TransactionDate.

Any ideas?

Thanks in advance and best

T

[PrashantSangle]

Hi

Try below 

SalesRep_Responsibilities:
Load *,ResponsibleProductGroupID&'_'&Right(ResponsibleTime,4) as key inline [
SalesRep, ResponsibleProductGroupID, ResponsibleTime
ABC, 123, 01.01.2019
DEF, 456, 01.01.2019
ABC, 456, 01.01.2020
DEF, 123, 01.01.2020
];

Transaction:
Load *,ProductGroupID&'_'&Right(TransactionDate,4) as key Inline [
TransactionDate, ProductGroupID,
01.04.2019, 123
01.04.2019, 456
01.03.2020, 123
01.03.2020, 456
];

Regards,

Prashant Sangle

[t_guet01]

Hi Prashant, 

thank you for your quick answer. My example is a clean world - real data look way more messy - responsibilities change every other day. Your code helps, if the years (or at least months) are unique - however, they are not.

Best

T

[PrashantSangle]

Can you share some sample data which cover most of your scenario.

[rwunderlich · Accepted solution]

I find it easiest to generate all the dates in between. Download this example

https://qlikviewcookbook.com/recipes/download-info/expand-a-pricing-date-table/

which given a start date for price, generates the price for all the dates in between.  Your example is identical, with SalesRep as the changing attribute instead of Price.

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[t_guet01]

Perfect, thanks a lot!

[t_guet01]

Hey Rob,

I followed the example from the cookbook to generate start and end dates.

When using a left join in combination with an intervalmatch, I receive a transaction table with my matching fields (startdate, enddate, and productID) from the intervalmatch. However, I actually only want to join the respective SalesRep (or Price in the cookbook example) to my transactions. How would I do that?

[rwunderlich]

I would not use intervalmatch in this case.  After generating the SalesRep table that has all the combinations of SalesRep, ProductId and Date,  Left join that table to your transaction table.  I assume transaction table has ProductId and Date. 

If you have more questions please post your relevant script. 

-Rob