Variables

[danielnevitt]

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

[Anonymous]

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

[stevedark]

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

[danielnevitt]

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

[stevedark]

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