Limit visible data + limit options in list box

[#1]

Hi,

I have a large data set with data on many customers, served by many transporters. I have connected this file to other data of two transporters. Their names are 'Malcolms' and 'Owens', and they are onder the field 'Carrier Name'.

Now I have two problems;

1) How do I make sure only the data of these two carriers shows in my graphs? Because the full data set with all transporters is connected. I learned a little bit of set analysis, and came to the following:

=Count({<[Carrier Name] = {Malcolms, Owens} >} DISTINCT [Customer Reference])

Problem is, I want to use a list box to switch between the two carriers, but with this formula, it always shows these two carriers no matter what.

2) My list box 'Consignee' now shows the names of all consignees served by all transporters which were in my original data set. I want it to only show the customers served by Malcolms and Owens. So not in grey, but just not visible at all. How do I do this?

Thanks in advance.

Niek

[datanibbler]

Hi Niek,

I'm sure you can solve this via set_analysis - that is really a great tool, there's virtually no limit to the things you can do.

However, it is resource-heavy and what you want to do is sure to be very complex syntax-wise.

=> If the requirements don't change too often, I would try limiting the data_load in the script.

=> Look up the "WHERE EXISTS" function to use with a LOAD.

==>> I assume those two (Owens and Malcolms) have a transporter_ID of sorts and that is the field via which you have linked the two tables, right?

==>> Then, when loading the other table (you need to have loaded the table with that field 'Carrier name' before the other), you can use a "WHERE EXISTS ([transporter_ID])" clause. Look it up, I'm not sure about the syntax.
==>> That way, only the data for those two will be loaded into your app in the first place and you don't have to mess with the remaining data_that_you_don't_want in the diagram.

HTH

Best regards,

DataNibbler

[Gysbert_Wassenaar]

1) If you want the listbox to behave as a toggle control you can first select a value in the listbox and then enable the option Always One Selected Value on the properties window of the listbox. That will make sure only one carrier can be selected at a time.

Show both carriers regardless of the selection in the listbox:

Count({<[Carrier Name] = {'Malcolms', 'Owens'} >} DISTINCT [Customer Reference])

Show only Owens regardless of the selection in the listbox:

=Count({<[Carrier Name] = {'Owens'} >} DISTINCT [Customer Reference])

Show the carriers selected in the listbox:

=Count( DISTINCT [Customer Reference])

2) change the listbox Consignee to use this expression instead: =aggr(Consignee, Consignee)

[jpapador · Accepted solution]

You could use indirect set analysis.  The first step would be to create another carrier name field with the only 2 values being Malcom and Owens.  Then your expression would look like this:

Count({<[Carrier Name] = P(NewCarrierNameFieldHere) >} DISTINCT [Customer Reference])

Carrier name will always equal the possible values of the newly created carrier name field (which should just be owens and Malcom.  This will also take into account selections should they be made in that field.

[#5]

Thanks jpapador, that worked for me. Do you also have a solution for my list box problem?

[jpapador]

In your load script you could do a left join onto the table that already contains the Consignee with a where clause where Match(Carrier Name, 'Malcolm', 'Owens') > 0.  That way it would load the field where Carrier name only equals those 2 values

So essentially it would look like this

Table1:

LOAD

<All your fields Listed Here>

FROM <Location>;

Left Join (Table1)

LOAD

      KeyFieldFromTable1,

      Consignee as ConsigneeNew

Resident Table1

Where Match([Carrier Name], 'Malcolm', 'Owens') > 0;