Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Thanks for your help marco. It was spot on