Spliting a string from one list and making a list box select from that list

[tinkerz1]

Hi,

I have 2 sets of locations from different spreadsheets but in different formats.

The first is in this format:

LISTA:

Europe,France,Germany,Greece,UK,Poland,South Africa,Turkey,Bermuda

Europe,France,Germany,Greece,

Europe

The second list is in the format:

LISTB:

Europe

France

Germany

UK

Greece

Belgium

I have manged to great a string from list B to select data from List A but if List B has no data I can not make a full string to look into List A and collect the data.

So I need to look at selecting LIST A's string of data and splitting it by comma's then make LIST B look for the individual countries.

Is it concat and AGGR?

Thanks.

[Digvijay_Singh]

Not sure though the exact need but see if this can help, the below script will load your first table as separate entries -

TableA:

Load Subfield(Country,',');

Load * inline [

Country

Europe,France,Germany,Greece,UK,Poland,South Africa,Turkey,Bermuda

Europe,France,Germany,Greece

Europe ](delimiter is '\n');

[tinkerz1]

Hi,

Very close, I have not explained very well either,

Spreadsheet A will carry a list of cells.

Groups

Europe,France,Germany,Greece,UK,Poland,South Africa,Turkey,Bermuda

Europe,France,Germany,Greece

Europe

Then in spreadsheet B:

Country Data

Europe

Greece

Wales

Germany

UK

I would like the user to click on the group field list and then if there is data for each of the countries select them all.

Is that possible?

What I need to happen is a full selection of spreadsheet A.

[sunny_talwar]

I think what Digvijay shared should work for you, you just need to add your fact table and make a connection between the two.

See if this is what you are looking for:

Table:

LOAD SubField(Country,',') as Country;

LOAD * Inline [

Country

Europe,France,Germany,Greece,UK,Poland,South Africa,Turkey,Bermuda

Europe,France,Germany,Greece

Europe

](delimiter is '|');

FactTable:

LOAD * Inline [

Country, GDP, Sales

Europe, 50, 25

Greece, 65, 32

Wales, 68, 31

Germany, 98, 50

UK, 90, 45

];

[Digvijay_Singh]

I think as per new explanation he is looking to select multiple countries in fact table based on selection from list A.

I think one way to do is as below (took fact data from sunny's script)

Use expressions in fact table like below

=Sum(if(SubStringCount(getfieldselections(GroupCountry,','),Country)=1,GDP))

Table:

LOAD * Inline [

GroupCountry

Europe,France,Germany,Greece,UK,Poland,South Africa,Turkey,Bermuda

Europe,France,Germany,Greece

Europe

](delimiter is '\n');

FactTable:

LOAD * Inline [

Country, GDP, Sales

Europe, 50, 25

Greece, 65, 32

Wales, 68, 31

Germany, 98, 50

UK, 90, 45

];

[sunny_talwar]

I guess to simplify things (front end expression), why not do this:

Table:

LOAD GroupCountry,

  SubField(GroupCountry, ',') as Country

Inline [

GroupCountry

Europe,France,Germany,Greece,UK,Poland,South Africa,Turkey,Bermuda

Europe,France,Germany,Greece

Europe

](delimiter is '\n');

FactTable:

LOAD * Inline [

Country, GDP, Sales

Europe, 50, 25

Greece, 65, 32

Wales, 68, 31

Germany, 98, 50

UK, 90, 45

];

Data model:

Best,

Sunny

P.S. I love how we work as a team

[tinkerz1]

=

if(SubStringCount(getfieldselections(Location,','),[Issue - Location])=1,[Market Risk Threshold Data])

If I sum the data I cant bring back the date dimension.

The problem is that I need all the selections to have data in them so the will match.

When I select Europe,France,Germany,Greece,UK,Poland,South Africa,Turkey,Bermuda from GroupCountry

The Fact table with country in it needs to have data for each of the countries.

Or it will match to a partial select and be incorrect.

[tinkerz1]

as I read that formula it says:

if this country from grouplocation is in country then return the data.

just looking for an extra qualifier to check if there is data associated with the country field

[sunny_talwar]

My detailed response is in moderation, but look at the attached file

[tinkerz1]

  What I am trying to say is:

if I concat the countries where they are in a single list

against 1 single selection where the countries are in one list seperated by ','

then give me the data.

(concat(distinct [Issue - Location],',')=GetFieldSelections(Location,',',getselectedcount ([Location]))

,if(SubStringCount(GetFieldSelections(Location,',',getselectedcount ([Location])),[Issue - Location])=1,[Data]))