How do I structure this table for optimal performance

[essalj666]

I have a table containing following information:

A: Existing table (about 20 mio recs)

A customer has 'DE' as tax_country no matter if which of the tax_country columns DE is entered in.

I want to be able to:

- count combinations of tax_countries

- count how many customers has a certain tax country etc.

I believe a restructure of data would be a feasible way.

Something like this:

add columns that contains 0 or one for all countries

B: very wide table

cus_id	country_001	Country_002	Country_003	Country_004	......	Country_246
1	0	0	1	0		1
2	1	1	1	0		1

But how do i get from A to B?

Or is there a smarter way to do this.

Or would a better solution be to have an array containing 247 country values (0 or 1) for every customer?

Could I sum on indivual countries then?

Thanhks for helping me out )

/L

[MarcoWedel · Accepted solution]

Hi,

I would go for a crosstable load to get a common country field that users could define combinations for in an And mode List Box.

One example:

mapCountryCodes:
Mapping
LOAD RecNo(),
    Code
FROM [https://en.wikipedia.org/wiki/ISO_3166-1_alpha-2] (html, unicode, embedded labels, table is @3);

tabTaxCountries:
LOAD RecNo() as cus_id,
    If(Rand()>0.5,ApplyMap('mapCountryCodes',Ceil(Rand()*249))) as tax_country_1,
    If(Rand()>0.5,ApplyMap('mapCountryCodes',Ceil(Rand()*249))) as tax_country_2,
    If(Rand()>0.5,ApplyMap('mapCountryCodes',Ceil(Rand()*249))) as tax_country_3,
    If(Rand()>0.5,ApplyMap('mapCountryCodes',Ceil(Rand()*249))) as tax_country_4,
    If(Rand()>0.5,ApplyMap('mapCountryCodes',Ceil(Rand()*249))) as tax_country_5
AutoGenerate 1000;

tabCountryTemp:
CrossTable (TaxCountry#, Country)
LOAD * Resident tabTaxCountries;

tabCountry:
LOAD Distinct
    cus_id,
    Country
Resident tabCountryTemp;

DROP Table tabCountryTemp;

hope this helps

regards

Marco

[essalj666]

Thanks for your help marco. It was spot on