topic Re: Efficient ways to load data that is in the wrong format? in QlikView

Efficient ways to load data that is in the wrong format?

Mon, 26 Mar 2012 08:18:40 GMT

I've got a file which has Product ID as the key, and then a series of columns, each one showing the price of that product in a different country.

What I'd like to do is have the price of the product as a dimension, as well as the country and Product ID. Currently I do this:

LOAD

Product_ID,

[Hong Kong] as Price,
'hk' as POS

FROM

(ooxml, embedded labels, table is productpricing0);

but I'm concerned that's not the best way to do it (there's about 100,000 rows in the file, and 20 different countries, so it's a bit of a challenge for my computer). Any suggestions?

Efficient ways to load data that is in the wrong format?

hic — Mon, 26 Mar 2012 08:29:31 GMT

Try

Crosstable (Country, Price, 1)

Load * From ... ;

Re: Efficient ways to load data that is in the wrong format?

Mon, 26 Mar 2012 08:45:25 GMT

Thanks Henric, but I think that's done the opposite of what I want.

I have an input file arranged like this:

ID	HK	SE
1	20	10
2	30	35
3	40	60
4	50	75

and what I want to get is three dimensions: ID, country and price (rather than ID, HK price and SE price).

I think if I wanted to go in the other direction, what you've given me would be just what I need... 😞

I've had a look at some other documentation (Qlikview's online help isn't very easy to navigate) and it looks like I could use resident tables to load the data much faster, but that still feels like a rather inelegant way to do it - I need a separate load from the resident table for each country that I add.

Efficient ways to load data that is in the wrong format?

hic — Mon, 26 Mar 2012 08:53:20 GMT

No, I think you should use Crosstable.

Crosstable (Country, Price, 1) Load ID, HK, SE From ... ;

An alternative approach is

For each vCountry in 'HK', 'SE'

Load ID, '$(vCountry)' as Country, [$(vCountry)] as Price From ... ;

Next vCountry

/HIC