Most efficient way to split tables

[#1]

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!

[jfkinspari]

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,

[Gysbert_Wassenaar]

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;

[#4]

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".

[#5]

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';