Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Most efficient way to split tables

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!

4 Replies
jfkinspari
Not applicable

Re: Most efficient way to split tables

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
Not applicable

Re: Most efficient way to split tables

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;


talk is cheap, supply exceeds demand
Not applicable

Re: Most efficient way to split tables

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

Not applicable

Re: Most efficient way to split tables

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