Apply Map or Alternative for creating a hierarchy?

[#1]

Hi,

I'm new to Qlikview and I'm trying to create a hierarchy of data for industries that map to the stock symbols in my data.  The problem is that I have a specific industry code for each stock which is 10 digits and then I have more generic industry categories that I would like to map the stocks within.  There are a total of 4 categories with the lowest level being the full 10 digits and the highest level being the first 4 digits of the 10; levels in between those are the first 6 and 8 digits.  See example below.

I have 2 tables in MySQL with the first providing the ticker symbol and the industry code (10 digits).  The second table includes only the industry code and the description.

Example for 2 Stocks:

Table1 (column names: ticker, code)

ticker: QLIK, code: 5720102010

ticker: RMBS, code: 5710101012

Table2 (column name: code, description)

code: 5720, description: Software & IT Services

code: 572010, description: Software & IT Services

code: 57201020, description: Software

code: 5720102010, description: Other Software

code: 5710, description: Technology Equipment

code: 571010: Semiconductors & Semiconductor Equipment

code: 57101012: Memory Chips (RAM)

Right now if I load the data into Qlikview there is no way to filter on the the first level by industry (e.g., first 4 digits) and see the next level, etc... Instead of being able to filter on 28 industries, I'm only able to see the data for the lowest level of industry classification (~837 industries).  I tried doing some MySQL joins but it becomes a nightmare and takes forever to load; not to mention I wasn't able to get it to work.

My current script would look something like:

co_industry:

SQL SELECT

     ticker, code FROM Table1;

industry_key:

SQL SELECT

    code, description FROM Table2;

I've put in about 3 hours trying to figure this out and I'm hoping it's something simple with the apply map functionality in Qlikview...

I have the personal desktop edition, so if anyone can help by posting the actual code that would be greatly appreciated.

Thanks in advance for the assistance!

- Ben

[Peter_Cammaert · Accepted solution]

This is example code that should demonstrate a few techniques:

// This is the original code to get source data

//CONNECT TO ....;

//

//co_industry:

//SQL SELECT

//    ticker, code FROM Table1;

//

//industry_key:

//SQL SELECT

//    code, description FROM Table2;

// This is my test data

co_industry:

LOAD * INLINE [

ticker, code

QLIK, 5720102010

RMBS, 5710101012

];

industry_key:

LOAD * INLINE [

code, description

5720, Software & IT Services

572010, Software & IT Services

57201020, Software

5720102010, Other Software

5710, Technology Equipment

571010, Semiconductors & Semiconductor Equipment

57101010, Memory Chips (RAM)

5710101012,

];

// First construct a ticker table with various useful hierarchy fields

// We assume a fixed code layout

Tickers:

NOCONCATENATE

LOAD ticker, code,

    left(code, 4) & '/' & left(code, 6) & '/' & left(code, 😎 & '/' & code AS HCode,

    left(code, 4) AS L1,

    left(code, 6) AS L2,

    left(code, 😎 AS L3,

    code AS L4

RESIDENT co_industry;

DROP Table co_industry;

// Separate levels and descriptions into a table for each level

FOR i = 1 to 4

  TLevel$(i):

  NOCONCATENATE

  LOAD code AS L$(i), description AS Descr_L$(i)

  RESIDENT industry_key

  WHERE len(code) = 2+$(i)*2;

NEXT

DROP Table industry_key;

On your Main sheet, create a listbox with field ticker (or a table or whatever). Also add listboxes for fields L1, L2, L3, L4. Add the description of the four Level codes as an expression to each listbox if you want.

Making selections in each Level listbox will get you what you want: a selection of all stock symbols belonging to one or more specific categories.

As an alternative, check out field HCode. To use it, create a listboxx with this field, and make sure to enable option "Show as TreeView" in Properties->General. You'll get a QlikView-supported hierarchy view (though the sublevels won't be selectable)

Best,

Peter

[#3]

Thank you!  You just saved me hours, if not days. Your code works perfectly and is a great example of the flexibility of Qlikview.

Thanks,

Ben

[#4]

One more question, sorry if I'm pushing it here as you already saved me tons of time.

I was going to create a straight table showing only the highest level (L1) and calculating key metrics for stocks within that industry (e.g., average ROE, average ROA, # of companies, etc...).  I can do that for L1 but I was also hoping to make it dynamic so once a user selected L1, L2 would show up.  Is there an easy way of doing that by adding L2 as a dimension and using "Enable Conditional" on the "Dimensions" tab under properties?

Thanks again,

Ben

[Peter_Cammaert]

You could add all levels with a Conditional Show that uses the GetSelectCount() function to check on how many selections have been made in a specific field. In your case, enable the L1 column if GetSelectedCount(L1) > 1, and enable the L2 column if GetSelectedCount(L1) = 1.

I'll leave it up to you to decide what to do with the other columns. It might get messy in those Conditional Show expression boxes