Clever Geek Handbook

Qlikview: how to create a pivot table to filter multiple related tables

I have 4-5 tables with one and multiple rows per ID. I want to create a pivot table that lists all ids as well as various count and max / mins values, but I want to be able to filter the calculations. Example: "Id" is an id and there are two tables, TestA and TestB.

One desired selection criterion: Show only those identifiers where at least one TestA score> 5 and there is at least one TestB result.

In a straight table, this is easy to do with expressions, but the result table cannot be selected for the computed true / false value.

I feel like I need to create a new table in the load script containing the id and then various conditions, marked as I want. Then these fields can be dimensions. This is similar to the concept of the main calendar. Am I on the right track?

If it helps to understand my example, this is a medical application; tables represent lab results and other interventions, each requiring complex queries, pulling data from different sources that are very "hardcoded" to create a small dataset from millions of rows of highly normalized source data. The desired measurements would be a combination of laboratories so that patients who met certain criteria could be identified - then, after filtering, there would be many more graphs and charts to determine what tests and procedures were performed on that group of patients.

My current data model loads many tables, which are then associated with an id. I tried loading all the data into one big table using concatenations and calculations, but that didn't seem to work for me with what I needed and was difficult to manage.

+3


source to share


1 answer


IIUC, I think what you want to do can be accomplished with a combination of sliders / input fields, variables and calculated dimensions in your table. The process is definitely cumbersome, but it should allow you to filter what you want.

  • Add the field to the table load statement in your script as rnum as RowNo()

    .
  • Create a variable for your filter (s). Ex. vFilterTestAScore

    ...

  • Add a slider or input field to your control panel and point to this variable.

    and. For a slider, the parameter is on the General tab → Data Header → select the Variable radio button.

    b. In the input field, add the correct list of variables from the list to the list of displayed variables.

  • Set the sliders / input fields to the criteria you want: vFilterTestAScore = 5

    andvFilterTestBScore = 1

  • Create a straight table with ID

    both dimensions and expressions for TestAScore

    and TestBScore

    . Expression formulas will be sum(TestAScore)

    and sum(TestBScore)

    accordingly (it will not make sense until the next step).

  • Now add the calculated dimension to the table. The idea here is that instead of having a dimension ID

    , you create a calculated dimension that only displays ID

    records that match the criteria you selected in the slider or type in the input field. The formula should be like this: if(aggr(sum(TestA), rnum) >= vFilterTestAScore, ID, null())

    or a number of filters: if((aggr(sum(TestA), rnum) >= vFilterTestAScore) and (aggr(sum(TestB), rnum) >= vFilterTestBScore), ID, null())

    .

  • In the new measured dimension, select the Suppress When Value Equals Null check box so that only the results that match your criteria are displayed in the table.



To summarize, you use variables to store your selection criteria, which you enter through an input field or slider. You then provisionally only show the ID in your table that meets those criteria using a calculated dimension and the Suppress At Zero option.

I can send you a .qvw if you are not using the free personal edition and can open other qvws.

+1


source






More articles:


All Articles