Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
Can you share some sample data which cover most of your scenario.
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
Perfect, thanks a lot!
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?
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