topic Storing Variable - GetFieldSelection? in QlikView

Storing Variable - GetFieldSelection?

Thu, 25 Jun 2009 00:57:23 GMT

Hello,

I have Department, and a list of trucks (Units) within that department.

I'm trying to store the list of trucks in a variable when the user selects that department. ie) select Department 'A', the variable will hold 'truck1,truck2,truck3' and so on.

Currently, I am using =GetFieldSelections (Units, ' , ', 30). However, if the user selects Department 'A' it does group the right truck, but since there are no trucks actually selected, nothing is being stored. I have to individually select the trucks for it to work.

I'm obviously not using the right command to do this 😄 but I couldn't find anything in the helpfile to do what I desire.

Thus is there a way to store the list of Units in a variable, based on the Department selection?

Thanks in advance.

Storing Variable - GetFieldSelection?

Thu, 25 Jun 2009 01:07:51 GMT

I didn't see a Get Field Possible Selections function either. I'm not sure if this can be done without macros.

It's easy with a macro. Set the OnSelect for your Department field to a macro. In the macro, you only need one line:

ActiveDocument.Fields("Units").SelectPossible

Then your GetFieldSelections command should work.

Storing Variable - GetFieldSelection?

Thu, 25 Jun 2009 02:19:35 GMT

It's Miller Time!!

Thanks, that worked like a charm.

Storing Variable - GetFieldSelection?

Thu, 25 Jun 2009 02:21:33 GMT

It's getting close.

Glad to help!

Storing Variable - GetFieldSelection?

rwunderlich — Thu, 25 Jun 2009 02:46:20 GMT


Chhai Sun wrote: I'm trying to store the list of trucks in a variable when the user selects that department. ie) select Department 'A', the variable will hold 'truck1,truck2,truck3' and so on.

You can get the list into a qlikview variable by defining the variable as:

=concat(Units, ',')

-Rob

Storing Variable - GetFieldSelection?

Thu, 25 Jun 2009 23:07:34 GMT

Thank you. Both suggestions worked. However, I'm having a bit of a problem calling the variable. For some reason the formula below does not work.


=Sum({$<UNIT_NUM={$(=subfield(vUNIT_ARRAY,',',1))},PM={'Y'}>}TOT_LABOR)/Production

However, when I put in the actual unit number the formula works(see below). I use =subfield(vUNIT_ARRAY),',',1) and it does display the right UNIT_NUM thus I can't figure out why the above statement does not work. It should be doing the exact same thing below


=Sum({$<UNIT_NUM={'72960-120'},PM={'Y'}>}TOT_LABOR)/Production

Storing Variable - GetFieldSelection?

Thu, 25 Jun 2009 23:19:30 GMT

I've always put the variable inside a dollar sign expansion. In yours, the variable is part of a larger dollar sign expansion. Could you try:

=Sum({$<UNIT_NUM={$(=subfield($(vUNIT_ARRAY),',',1))},PM={'Y'}>}TOT_LABOR)/Production

Does putting a dollar sign expansion inside a dollar sign expansion cause a tear in the space time continuum?

Storing Variable - GetFieldSelection?

Fri, 26 Jun 2009 01:25:35 GMT

I tried that but no change on the space-time continuum since I'm getting the same result 😄

I wonder who would be able to solve this faster, Data or Chekov 😄

Storing Variable - GetFieldSelection?

Fri, 26 Jun 2009 01:41:17 GMT

Do you have that Expression in a chart? I just noticed a good way to check how your dollar sign expansion is working. Use a straight table and add the expression. Don't label the expression, keep it as <use expression>. Then when the table is rendered, the label for that expression will be the expression after the dollar sign expansion is evaluated. For yours, you may need to wrap the headers to a few lines to see it all. That may allow you to see how your dollar sign expansion is working.

Since the subfield expression works on its own, I'm guessing there is a problem in that portion of the larger expression.

EDIT: On second thought, I don't know what could be wrong. I just tested out the subfield function using a variable inside a dollar sign expansion and it worked as expected.

Sum( {1<Company = {$(=Subfield(vSel, ',', 1))}>} Amount)

Storing Variable - GetFieldSelection?

johnw — Fri, 26 Jun 2009 01:49:20 GMT


NMiller wrote:I just noticed a good way to check how your dollar sign expansion is working. Use a straight table and add the expression. Don't label the expression, keep it as <use expression>. Then when the table is rendered, the label for that expression will be the expression after the dollar sign expansion is evaluated. For yours, you may need to wrap the headers to a few lines to see it all. That may allow you to see how your dollar sign expansion is working.

Yep. I do that all the time. It's a good debugging tool.

Storing Variable - GetFieldSelection?

Fri, 26 Jun 2009 01:53:08 GMT

Instead of continually editing my post, I'll make a new one.

I think what is happening is your vUnit_Array value is being evaluated as an expression. i.e. 72960-120 = 72840. Since none of your UNIT_VALs are 72840, you are getting nothing.

The expression seems to allow quotes around the dollar sign expansion. Try this:

=Sum({$<UNIT_NUM={'$(=subfield(vUNIT_ARRAY,',',1))'},PM={'Y'}>}TOT_LABOR)/Production

Storing Variable - GetFieldSelection?

Fri, 26 Jun 2009 02:14:43 GMT

Miller (aka number one),

thanks a lot. You must of passed Star Fleet Academy no problem.

Storing Variable - GetFieldSelection?

Fri, 26 Jun 2009 02:14:47 GMT

Miller (aka number one),

thanks a lot. You must of passed Star Fleet Academy no problem.

Storing Variable - GetFieldSelection?

Fri, 26 Jun 2009 02:36:05 GMT

Nope, I flunked out for spending too much time on the Internet!