topic Re: Spliting a string from one list and making a list box select from that list in QlikView

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

tinkerz1 — Fri, 16 Oct 2015 11:41:57 GMT

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

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.

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

Digvijay_Singh — Fri, 16 Oct 2015 12:09:17 GMT

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');

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

tinkerz1 — Fri, 16 Oct 2015 12:46:58 GMT

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

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.

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

sunny_talwar — Fri, 16 Oct 2015 12:54:21 GMT

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

];

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

Digvijay_Singh — Fri, 16 Oct 2015 13:54:20 GMT

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

];

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

sunny_talwar — Fri, 16 Oct 2015 13:59:56 GMT

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

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

tinkerz1 — Fri, 16 Oct 2015 14:14:56 GMT

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.

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

tinkerz1 — Fri, 16 Oct 2015 14:24:01 GMT

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

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

sunny_talwar — Fri, 16 Oct 2015 14:29:45 GMT

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

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

tinkerz1 — Fri, 16 Oct 2015 14:38:20 GMT

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]))

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

tinkerz1 — Fri, 16 Oct 2015 14:41:43 GMT

So this works if I select both lists

(concat(distinct [Issue - Location],',')=GetFieldSelections(Location,',',getselectedcount ([Location])),[Market Risk Threshold Data])

No I just have to make the connections between A and B automatically as before

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

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

sunny_talwar — Fri, 16 Oct 2015 14:46:05 GMT

Have you checked this application?

May be this is what you are looking for

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

tinkerz1 — Fri, 16 Oct 2015 15:32:37 GMT

Not quite what I am after.

When I select the single list of countries I want to find its match in the group of countries.

So if I select:

France

Germany

The concat string is France,Germany

I would like this information to be matched in the group of countries from spreadsheet A.

And if I select a field from spreadsheet A that looks like

Europe,Germany,UK.

I want that data to be selected in the single list of countries from spreadsheetA

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

tinkerz1 — Fri, 16 Oct 2015 15:45:54 GMT

if(concat(distinct [Issue - Location],',')=[Location],[Market Risk Threshold Data])

This is working nicely, and finds the selections, just looking for a solution the other way around,

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

tinkerz1 — Fri, 16 Oct 2015 16:06:53 GMT

Yes thanks Sunny for this, I almost have the answer,

When using subfield the data is split on a one to many relationship. so if I select fact table country I return all the GroupCountry data a country is part of.

I need to match they complete selection once from the fact table

and if i select group count once, return those countries from the fact table,

in your relationship i will pull out any group country list France is a part of.

If France is a single selection I just want that, if I select France and Germany, I just want France,Germany from the grouptable.

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

sunny_talwar — Fri, 16 Oct 2015 16:09:24 GMT

Oh so you are saying that you are looking for a and search. So show all those within GroupCountry where it matches with Country1 and Country2

So if Europe and Germany is selected you want to see these two rows seleced in GroupCountry?

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

Europe,France,Germany,Greece

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

tinkerz1 — Fri, 16 Oct 2015 16:12:02 GMT

a complete match Sunny

Europe,Germany is not in that list.

I hope you see it now

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

sunny_talwar — Fri, 16 Oct 2015 16:13:02 GMT

That make sense. Let me see if I can work this out

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

sunny_talwar — Fri, 16 Oct 2015 16:15:59 GMT

I am not sure how we can do this. May MarcoWedel‌ might have a work around he can think of. I am going to very interested in knowing this for myself as well.

Best,

Sunny

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

tinkerz1 — Fri, 16 Oct 2015 16:27:10 GMT

I am thinking a link table is needed after both tables are loaded.