topic Re: Variables in QlikView

Variables

danielnevitt — Wed, 26 Mar 2014 12:46:18 GMT

Hi,

I have written the below calculated dimension, in order to restrict the data based on the vCountry variable:

IF(MATCH(trim(LOAD_LOC_REF),(vCountry)),LOAD_LOC_REF,
IF(MATCH(trim(DISCH_LOC_REF),(vCountry)),DISCH_LOC_REF,null())
)

This works perfectly if I then enter for example USA in the input box. It would be appreciated if someone could advise the following:

1) How can I enter more than one value eg USA, CAN, BRA etc?

2) How can I select all countries? Is there a code, or should I create a bookmark?

Thanks for any help with this issue.

Regards,

Daniel

Re: Variables

its_anandrjs — Wed, 26 Mar 2014 12:57:25 GMT

Hi,

Create a variable like VCountry and put default value USA and take input box and type value like see snap shot.

Hope this helps

Thanks & Regards

Re: Variables

its_anandrjs — Wed, 26 Mar 2014 13:04:23 GMT

And for 2 better you create a Bookmark.

Hope this helps

Thanks & Regards

Re: Variables

danielnevitt — Wed, 26 Mar 2014 13:05:37 GMT

Hi Anand,

Thank you for your reply.

I am still slightly confused how I can select all countries?

Regards,

Daniel

Re: Variables

danielnevitt — Wed, 26 Mar 2014 13:09:32 GMT

I have created the bookmark (BM07). How can I enter this as a selection?

Also is there a way to rename the bookmark to say 'ALL'?

Thanks,

Daniel

Re: Variables

its_anandrjs — Wed, 26 Mar 2014 13:13:11 GMT

You have to use Bookmarks for that in input box i believe not possible.

Hope this helps

Thanks & Regards

Re: Variables

javier_florian — Wed, 26 Mar 2014 13:13:58 GMT

Hi Daniel,

For what do you need work with this variable? Can you try with reduction data? With reduction data you can enable to users to see only related information. See reference manual.

-JFlorian

Re: Variables

danielnevitt — Wed, 26 Mar 2014 13:20:16 GMT

Hi Javier,

I am trying to allow the user to make a selection where either the load or discharge country is a specific location.

The user may also wish to only select a specific company, therefore I need to be able to also select all locations.

As stated above, I have created a bookmark for this. However I am not sure how you select a bookmark in an input box.

Regards,

Daniel

Re: Variables

Anonymous — Wed, 26 Mar 2014 13:29:23 GMT

if you can use listbox instead of an input box, then you can check out this thread:

Bookmarks in ListBox

Re: Variables

danielnevitt — Wed, 26 Mar 2014 14:08:16 GMT

Hi,

Is there no way to select all values for an input box variable?

Regards,

Daniel

Re: Variables

Anonymous — Wed, 26 Mar 2014 14:22:30 GMT

sorry, i'm not sure it is possible...may be someone else can confirm that.

Re: Variables

stevedark — Wed, 26 Mar 2014 14:29:34 GMT

Hi Daniel,

But not selecting any value in a listbox you are effectively selecting all (except rows where there are nulls). To select all just click a listbox and type * and then hit enter. A better approach would be to have a separate field created on load that has a yes/no selection for countries being on your list:

IF(MATCH(trim(LOAD_LOC_REF),'$(vCountry)'),'Yes', 'No') as [Country On List],

You can then add that as a separate list box.

Also, you will get better performance from the list box if you calculate the field on load rather than use an expression in the list box, for example:

IF(MATCH(trim(LOAD_LOC_REF),'$(vCountry)'),LOAD_LOC_REF, null()) as Country,

In both of those cases you can do the nested IF on the load script to deal with your pair of location fields.

A way of selecting without a bookmark is to have a button (I tend to use textboxes with actions) that applies the selection. Simply create teh text box, create a Select In Field action, give the name of your field (eg. Country) and then set the value to be:

("USA"|"CAN"|"BRA")

This approach will only work with fields, and not calculated expressions - so you would need to create the correct field value on load for that to work.

Hopefully that will help move you on a bit.

Cheers,

Steve

Re: Variables

danielnevitt — Wed, 26 Mar 2014 14:51:10 GMT

Hi Steve,

Thank you for the detailed reply.

Unfortunately when I enter * in the input box, it doesn't return any data.

I understand how your suggestion above would work if I was only looking at one field (ie LOAD_LOC_REF). However I am unsure how to write the code to also look at DISCH_LOC_REF ie where 'selection is LOAD_LOC_REF OR DISCH_LOC_REF

Perhaps I am approaching this issue incorrectly. Basically all I am trying to do is have an input/selection box where the user can select either one, two, three or all countries which match either the load OR discharge location for the trade.

Is anyone able to attach an example file as to how this can be achieved?

Thanks,

Daniel

Re: Variables

stevedark — Wed, 26 Mar 2014 14:57:16 GMT

Hi Daniel,

You will need to create another table in your data model for this. Does your current table have an ID?

If so, the code you need to insert after your existing load will look a bit like:

EitherCountry:

LOAD

ID,

LOAD_LOC_REF as [Either Country]

RESIDENT ExistingTable

WHERE NOT IsNull(LOAD_LOC_REF)

;

EitherCountry:

LOAD

ID,

DISCH_LOC_REF as [Either Country]

RESIDENT ExistingTable

WHERE NOT IsNull(DISCH_LOC_REF)

;

The Either Country field can then be used in a list box.

The WHERE clause could include your MATCH statement if you are only interested in countries on your list. Obviously you will need to change the table and field names to match your data model and requirements.

Not sure where the Input Box comes into things here, but the * approach should work with any List Box.

Cheers,

Steve