Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Please help with an advice as I am pretty new to qlik sense
For each customers we have different promotions for different products.
When loading the customer database, I want to obtain a new column called "Final Promo" based on the following rules:
- I am keeping only the codes endind in 15, 16 or 17 with the priority in reverse (highest number will be kept)
-Removing duplicates, if the same code apears more than once in the columns Promo 1 to Promo 4
Thanks in advance for your support
Customer | Promo1 | Promo2 | Promo3 | Promo4 | Final Promo |
1 | TLS_916 | ExtraComp150 | TLS_916 | ||
2 | SOHO_SPR2011_INT | - | |||
3 | AC100 | AC100 | AC100 | - | |
4 | TLS_15 | TLS_15 | TLS_15 | TLS_15 | |
5 | TLS417 | TLS417 | TLS417 | TLS417 | |
6 | TLS_15 | TLS417 | TLS417 |
By transforming the source customer table of 6 rows by 5 columns into a 24 rows by 3 columns it will be easier to create the logic to pick the right promo for each customer:
LOAD SCRIPT:
Then using a Pivot Table and the expression
SubField( Concat({<Code={"*15","*16*","*17"}>} Code,'|' , -Right(Code,2) ) ,'|' , 1 )
for the Final Promo measure you can get this:
I have attached a QVF Qlik Sense application that these screenshots are taken from...