combining columns and removing duplicates

[maciverzi]

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

[petter]

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