CONCAT distinct picking too many values

j_nlucas — Mon, 07 May 2018 14:12:42 GMT

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

Re: CONCAT distinct picking too many values

sunny_talwar — Mon, 07 May 2018 14:24:44 GMT

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

topic CONCAT distinct picking too many values in QlikView

CONCAT distinct picking too many values

Re: CONCAT distinct picking too many values