Hi Qlik Community,
I have a task where I'm having troubles coming up with the concept on how to write the script in Qlik Sense Data Model. I'm hoping for someone to shed some light on this. Basically I have a dataset of order details with fields such as Bill To, Ship To, Customer Part Num, Marketing Part Num, PO Num, Reported End Customer, Translated Customer, Parent ID, etc. and I have a rule file to annotate these orders into a specific customer name based on the rule parameters set up. The rule file is made up of fields that exists in the transactions and is set up in priority sequence. For example:
Example Rule File and few basic rules of the rule file:
- The rule file is set up as the below example with only certain fields being filled in with a specific value.
- % sign denotes contains, similar to how * works in Qlik. It is placed either at the beginning only, at both front and back, back only or none at all. Such condition of the rule parameters are not specifically grouped together in the rule file and can be placed anywhere.
- The rule file is also set up in a specific priority and should be run in ascending order.
- For example, Rule ID#1 would mean that if the Bill To equals to ABC and Marketing Part Num begins with MM7879 in the transaction then return Final Customer Name = Amy and Annotated Rule ID = 1.
- Rule ID#2 would mean that if the Customer Part Num contains DN7654 and Reported End Customer begins with BRA then return Final Customer Name = Brad and Annotated Rule ID =2.
| Rule ID | Final Customer Name | Bill To | Customer Part Num | Marketing Part Num | PO Num | Reported End Customer | Translated Customer | Parent ID | Ship To |
| 1 | Amy | ABC | | MM7879% | | | | | |
| 2 | Brad | | %DN7654% | | | BRA% | | | |
| 3 | Dan | | | | | | %DAN | | |
| 4 | Lily | | | | | LILY - GERMANY% | | | |
Each transaction has to run the rule file in the priority order until a condition in the rule has been met to return the Final Customer Name and the Rule ID, then skip to the next transaction to continue the same action until all the Final Customer Name and the Rule ID has been determined. If there's any transaction that does not meet any of the rule conditions set up, then return others.
Anyone has any ideas how to set this up in Qlik Sense data model? Would love to hear some great ideas!
Thanks,
Elin
