topic Re: Most efficient way to split tables in QlikView

Most efficient way to split tables

Mon, 03 Dec 2012 12:24:04 GMT

Hi Qview users,

What is the most efficient way to split one table containing a mixed bag of what I will call master data into separate entities to be consumed by QV?

For instance, consider a table that has ID (key) / TYPE (key) / VALUE.

For instance, 1/ COUNTRY / United States, and 2/ COUNTRY / Canada, would both belong to this table, so would 3/ CURRENCY / USD, and 4/ CURRENCY / CAD.

How could currencies and countries be splitted efficiently, that is without multiplying queries to the data source?

Thanks!

Re: Most efficient way to split tables

jfkinspari — Mon, 03 Dec 2012 12:27:59 GMT

Hi,

If your focus is on reducing the number of queries to the data source, you could start by loading the complete table into QV, and use residents load from there on to split the table by using simple selective where queries or doing some looping on the TYPE field.

Regards,

Re: Most efficient way to split tables

Gysbert_Wassenaar — Mon, 03 Dec 2012 12:33:08 GMT

It sounds like your source table is what's called a generic database. So, you could try using a generic load:

generic select * from sometable;

Re: Most efficient way to split tables

Tue, 04 Dec 2012 00:00:54 GMT

Hi Gysbert,

I had a look at that, a generic load is accomplishing a pivot which is not what I am looking for. I am just interested in splitting a generic table into meaning ful chunks that resembles "dimensions".

Re: Most efficient way to split tables

Tue, 04 Dec 2012 00:08:25 GMT

Hi Jens,

That's what I have done. It's working. I could not find how to do inserts on resident tables, see the first bullet point below.

These are the two scenarios considered:

Row-based inserts

ForvValueNo = 1 toNoOfRows('mytable')
LET vConstantValue = peek('TYPE', $(vValueNo)-1,'mytable')

<IF TYPE = 'COUNTRY' THEN INSERT TO RESIDENT TABLE>
nextvValueNo

Multiple LOADs on a large LOAD

[Master Table]:

SQL SELECT <select statement>

          [Country Table]:
          Load Company,Division, ConstantValue,Description resident [Master Table]
          where ConstantValue = 'COUNTRY';