Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
maciverzi
Not applicable

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
petter
Not applicable

Re: combining columns and removing duplicates

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...