Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Partner
Partner

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

Re: CONCAT distinct picking too many values

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