Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
essalj666
Contributor II
Contributor II

How do I structure this table for optimal performance

I have a table containing following information:

A: Existing table (about 20 mio recs)

tbl_tax_country.png

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_idcountry_001Country_002Country_003Country_004......Country_246
100101
211101

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

1 Solution

Accepted Solutions
MarcoWedel

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:

QlikCommunity_Thread_313487_Pic1.JPG

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

View solution in original post

2 Replies
MarcoWedel

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:

QlikCommunity_Thread_313487_Pic1.JPG

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
Contributor II
Contributor II
Author

Thanks for your help marco. It was spot on