Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

combining columns and removing duplicates

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

CustomerPromo1Promo2Promo3Promo4Final Promo
1TLS_916ExtraComp150 TLS_916
2 SOHO_SPR2011_INT -
3AC100AC100AC100 -
4TLS_15TLS_15TLS_15 TLS_15
5 TLS417TLS417TLS417TLS417
6TLS_15TLS417TLS417
1 Reply
Highlighted
MVP
MVP

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:

2018-01-11 19_50_50-Qlik Sense Desktop.png

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:

2018-01-11 19_47_41-Qlik Sense Desktop.png

I have attached a QVF Qlik Sense application that these screenshots are taken from...