topic Re: Set Analysis: Get distinct values in QlikView

Set Analysis: Get distinct values

Mon, 31 Mar 2014 14:17:54 GMT

Hi Experts,

I am held up with the below problem.

I have (say) 20 zipcodes, of which 8 zipcodes(4 distinct) are of 'Type'=Store and others are of type 'Sales'. I wish to get the all the values of type 'store' in a variable and assign to each of them with with a color code.

How do i achieve it? Should I use if statement or Set Analysis?

Thanks for your time.

Regards,

Pramod K

Re: Set Analysis: Get distinct values

Gysbert_Wassenaar — Mon, 31 Mar 2014 14:24:22 GMT

You can put this expression in a variable: =concat({<Type={store}>}distinct zipcode, ',' )

Re: Set Analysis: Get distinct values

MK_QSL — Mon, 31 Mar 2014 14:24:45 GMT

Where exactly you want to use this color?

I.e. Straight Table or in any other object?

Re: Set Analysis: Get distinct values

datanibbler — Mon, 31 Mar 2014 14:30:12 GMT

Hi Pramod,

both ways are possible.

I would suggest using an IF_statement, just because set_analysis is tricky. That would look sth. like this

>>> = IF((type = 'store' and [zipcode] = '12345'), [your_colour]) <<<

or, maybe even simpler,

>>> = IF(type = 'store',

PICK(MATCH([zipcode], [code1], [code2]), [colour1], [colour2])) <<<

=> That way, the type will be queried first and the inner query will only ever be executed for those codes of type 'store' to begin with - that might be more efficient resource-wise.

HTH

Best regards,

DataNibbler

Re: Set Analysis: Get distinct values

Mon, 31 Mar 2014 16:27:27 GMT

I will be using these color codes in the Color expression on Qlikmap..

Re: Set Analysis: Get distinct values

Mon, 31 Mar 2014 16:40:35 GMT

Thank you very much.. it worked