topic Re: How do I structure this table for optimal performance in QlikView

How do I structure this table for optimal performance

essalj666 — Mon, 10 Sep 2018 14:27:32 GMT

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 )

Re: How do I structure this table for optimal performance

MarcoWedel — Tue, 11 Sep 2018 22:35:46 GMT

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

Re: How do I structure this table for optimal performance

essalj666 — Fri, 21 Sep 2018 00:17:04 GMT

Thanks for your help marco. It was spot on