Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
j_nlucas
Partner - Contributor III
Partner - Contributor III

CONCAT distinct picking too many values

Hi,

I have the following expression:

=CONCAT({1< Month=, VirtualProduct=P(Product_code)>}DISTINCT VirtualProduct & ', - ' & VirtualMonth &  ' - ' & If(Flag_Bloquear,',,-1,,-1,,-1,,-1',Locking_code)& CHR(10))

The purpose is to get a list of 12 months per product, and a code that is unique per month and will depend on the flag Flag_Bloquear.

The field "Locking_code" has two possible values: ',,-1,,-1,,0,,0' and ',,0,,0,,-1,,-1' and it will pick one or another depending on the selections in one field, also with two possible values that is in the same table.

This said, the list should be something like:

Product1, 01, ',,-1,,-1,,-1,,-1'

Product1, 02, ',,-1,,-1,,0,,0'

Product1, 03, ',,0,,0,,-1,,-1' and so forth until month 12, and then next product.

However,  I am getting three values per month and product. Each of the lines belongs to all the possible conditions handled in the If statement. See below:

90110323, - 1 - ,,-1,,-1,,-1,,-1

90110323, - 1 - ,,-1,,-1,,0,,0

90110323, - 1 - ,,0,,0,,-1,,-1

90110323, - 10 - ,,-1,,-1,,-1,,-1

90110323, - 10 - ,,-1,,-1,,0,,0

90110323, - 10 - ,,0,,0,,-1,,-1

90110323, - 11 - ,,-1,,-1,,-1,,-1

90110323, - 11 - ,,-1,,-1,,0,,0

90110323, - 11 - ,,0,,0,,-1,,-1

90110323, - 12 - ,,-1,,-1,,-1,,-1

90110323, - 12 - ,,-1,,-1,,0,,0

90110323, - 12 - ,,0,,0,,-1,,-1

90110323, - 2 - ,,-1,,-1,,-1,,-1

90110323, - 2 - ,,-1,,-1,,0,,0

90110323, - 2 - ,,0,,0,,-1,,-1

90110323, - 3 - ,,-1,,-1,,-1,,-1

90110323, - 3 - ,,-1,,-1,,0,,0

90110323, - 3 - ,,0,,0,,-1,,-1

90110323, - 4 - ,,-1,,-1,,-1,,-1

90110323, - 4 - ,,-1,,-1,,0,,0

90110323, - 4 - ,,0,,0,,-1,,-1

90110323, - 5 - ,,-1,,-1,,-1,,-1

90110323, - 5 - ,,-1,,-1,,0,,0

90110323, - 5 - ,,0,,0,,-1,,-1

90110323, - 6 - ,,-1,,-1,,-1,,-1

90110323, - 6 - ,,-1,,-1,,0,,0

90110323, - 6 - ,,0,,0,,-1,,-1

90110323, - 7 - ,,-1,,-1,,-1,,-1

90110323, - 7 - ,,-1,,-1,,0,,0

90110323, - 7 - ,,0,,0,,-1,,-1

90110323, - 8 - ,,-1,,-1,,-1,,-1

90110323, - 8 - ,,-1,,-1,,0,,0

90110323, - 8 - ,,0,,0,,-1,,-1

90110323, - 9 - ,,-1,,-1,,-1,,-1

90110323, - 9 - ,,-1,,-1,,0,,0

90110323, - 9 - ,,0,,0,,-1,,-1

  • VirtualProducts: Is the only field in a data island.
  • VirtualMonth is in a different table (VirtualMonths)
  • Flag_Bloquear has two values, 0 and 1 and is in the same table as VirtualMonth
  • product_code: is in the fact table, which is linked to VirtualMonths via Month

Is there a way to achieve this?

Thank you

Joaquin

1 Reply
sunny_talwar

Where exactly are you using this Concat function? In a straight table or pivot table or in a text box object?