Show the best option

[#1]

Dears,

I need to show the best location to create a services store. So in order to know our customers, we've sent a survey through our customers and asked than to fill some fields with best location for than.

So, basically I have an excell spreadsheet with 6 collumns. like this:

City1	Level	City2	Level2	City3	Level3
CityA	High	CityC	Low	CityB	High
CityB	Mediun	CityA	Low	CityC	Low
CityC	Low	CityA	High	CityB	High
CityA	Medin	CityB	Low	CityD	Low
CityC	Low	CityD	Mediun	CityA	High

The City1 is the first (best) option for him, and Level is how interesting is he on our services at this location.

The City2 is the secound (secound-best) option for him, and Level2 is how interesting is he on our services at this location.

The City3 is the last, but still interesting (third-best) option for him, and Level3 is how interesting is he on our services at this location.

Basically, the calculum will be:

Count how many times the level "High" appears for each city, and show in a pizza chart the cities and the number of High. If it was converted in a table, iw tould be something like this:

City	Number
CityA	3
CityB	2
CityC	0
CityD

How can I do it?

Thanks for your attention

Best regards,

Bruno Lelli

[#2]

Hi Bruno, see attached example.

Regards

[#3]

Check what I'm doing...

//Para a melhor opção de localização

LOAD [Primeira opção de localidade:] as Location,
  [Nivel1?] as Level,
  [Segunda opção de localidade:] as Location,
  [Nivel2?] as Level,
  [Terceira opção de localidade:] as Location,
  [Nivel3?] as Level
FROM (biff, embedded labels, table is [Expansão EOSC Brasil$]);

and it is giving me the error:

How can I do it?

Thanks

[#4]

Hi, you cannot load all the fields at once, you have to load them pair by pair, so automatic concatenation takes place, like this:

LOAD [Primeira opção de localidade:] as Location,
  [Nivel1?] as Level

FROM (biff, embedded labels, table is [Expansão EOSC Brasil$]);

Load

  [Segunda opção de localidade:] as Location,
  [Nivel2?] as Level

FROM (biff, embedded labels, table is [Expansão EOSC Brasil$]);

Load [Terceira opção de localidade:] as Location,
  [Nivel3?] as Level
FROM (biff, embedded labels, table is [Expansão EOSC Brasil$]);

Regards

[christian77]

Hi:

Retrieve the coordinates of each adress. Use Pitagoras' distance between two points.

That's it for distances.

About level: Biuld yourself a funcktion to describe the problem. Ponderate Level and Distance with an factor (each one) and see the result.

Choose the optimun.

U may want to use the RADAR GRAPHIC. Set on each axe that caracteristic that you want to measure. Ponderate and Escale properly.

The optimun solution is that with the largest area.

I used the radar graphic to look for apartment. It really works.

Ok ducky.

[stigchel]

You could also use a crosstable:

CrossTable(City, Data, 1)

load

rowno() as ID,

if(City1='CityA',Level) as CityA,

if(City2='CityA',Level2) as CityA,

if(City3='CityA',Level3) as CityA,

if(City1='CityB',Level) as CityB,

if(City2='CityB',Level2) as CityB,

if(City3='CityB',Level3) as CityB,

if(City1='CityC',Level) as CityC,

if(City2='CityC',Level2) as CityC,

if(City3='CityC',Level3) as CityC

FROM Test.xls (biff, embedded labels, table is Sheet1$);

You might want to consider weighting the results based on first, second.... choice. In that case, replace the ",Level" in the lines by an expression that e.g. calculates a score from 9 to 1 (1st, high to 3rd low)