topic Re: Please advice! Extract dependent value from column in QlikView

Please advice! Extract dependent value from column

Mon, 04 May 2015 17:27:20 GMT

Hello,

I have an access database linked in Qlikview with various tables and those tables have relationships(via primary key etc). For my case, consider Columns A B C and D. The user selects inputs from A, B and C ; these selections give the respective value from column D. I am having trouble extracting the value from column D. I thought of using a macro to extract the value in question; for which i need the exact cell number(row and column number combination). Any suggestions so as to retrieve the value that D reflects? or alternatively the cell number of the value in Column D ? Thank you, All your efforts are highly appreciated. Please advice?

Regards,

Nik

Re: Please advice! Extract dependent value from column

sunny_talwar — Mon, 04 May 2015 17:38:14 GMT

Not sure what you mean by extracting here. Would you be able to share a sample and expected output?

Best,

Sunny

Re: Please advice! Extract dependent value from column

Mon, 04 May 2015 17:42:49 GMT

A	B	C
1	2	3

Thanks for responding,

Find above an attempt to explain my case,

I have 4 columns A B C and D ,

when the user selects 1 2 3 from A B and C respectively

the value (marked in yellow from Column D is selected) ,

I need to extract that particular value. (have that value to be displayed in a text box or stored in a variable)

Best,

Nik

Re: Please advice! Extract dependent value from column

sunny_talwar — Mon, 04 May 2015 17:49:56 GMT

A, B, C, D are all part of a same table? and its hard to understand the table you have built, but is it something like this?

A	B	C	D
0	0	0	1
0	0	0	2
0	0	0	3
1	2	3	4
0	0	0	5
0	0	0	6
0	0	0	7
0	0	0	8
0	0	0	9
0	0	0	10

Where 0 can be replaced with other values?

Re: Please advice! Extract dependent value from column

ramoncova06 — Mon, 04 May 2015 17:50:56 GMT

you can use this in your textbox, the assumption is that a,b,c have to be selected, otherwise you can switch the 'and' to 'or'

=if(GetSelectedCount(a)>0 and GetSelectedCount(b)>0 and GetSelectedCount(c) >0, concat(d,','))

Re: Please advice! Extract dependent value from column

Mon, 04 May 2015 17:53:24 GMT

My apologies for a poorly constructed table. What you have is accurate, where the 0s can be replaced my any random values.

Thanks,

Re: Please advice! Extract dependent value from column

Mon, 04 May 2015 17:55:15 GMT

The statement GetSelectedCount(a)>0 signifies that a value from A has been selected? Is that accurate?

Also, what if i have multiple values from A B and C selected which give me 2 rows in D.

Appreicate your help, Thanks.

Re: Please advice! Extract dependent value from column

sunny_talwar — Mon, 04 May 2015 17:55:42 GMT

There are couple of ways you can extract a value. One of them is rightly pointed out by Ramon Covarrubias‌ here. Another way if you know what your A, B, C values are, then you can use something like this:

Only({<A = {1}, B = {2}, C = {3}>} D)

HTH

Best,

Sunny

Re: Please advice! Extract dependent value from column

Mon, 04 May 2015 17:58:10 GMT

Thank you for your responses Sunny and Ramon, I will try your suggestion and write back if i need help. Thank you again.

Best,

Nik

Re: Please advice! Extract dependent value from column

ramoncova06 — Mon, 04 May 2015 17:59:00 GMT

The statement GetSelectedCount(a)>0 signifies that a value from A has been selected? Is that accurate? [RC] Yes

Also, what if i have multiple values from A B and C selected which give me 2 rows in D [RC] both will show separated by a ","

Re: Please advice! Extract dependent value from column

sunny_talwar — Mon, 04 May 2015 18:00:26 GMT

No problem bud! Always here to help.

Best,

Sunny

Re: Please advice! Extract dependent value from column

Mon, 04 May 2015 18:15:41 GMT

Hi,

Is there a way i can assign the extracted value to a variable using a macro or otherwise? something using a 'set' statement ? Thanks,

sunindia‌

Re: Please advice! Extract dependent value from column

sunny_talwar — Mon, 04 May 2015 18:18:35 GMT

You can do this by going in your Variable Overview (Ctrl + Alt + v) and create a new variable

vVar = Only({<A = {1}, B = {2}, C = {3}>} D) or if(GetSelectedCount(a)>0 and GetSelectedCount(b)>0 and GetSelectedCount(c) >0, concat(d,','))

What is your end goal, if you don't mind me asking? Because we might be able to help you better if we know what you are trying to do.

Best,

Sunny

Re: Please advice! Extract dependent value from column

Mon, 04 May 2015 19:29:38 GMT

Sunny,

This was the end game. I needed to access that value in a variable. To be honest it was a stupid question to ask how to set it to a variable especially after i have the definition statement. I appreciate your patience, Thanks a lot.

Re: Please advice! Extract dependent value from column

sunny_talwar — Mon, 04 May 2015 19:38:32 GMT

Nothing is stupid my friend. We all are learning, I actually learn more as I work on stupid things. So keep asking and keep trying.

Best,

Sunny