Table to show number of possible selections for every data field considering a given selection

[Henke_Chr]

I have a table with more than 350 fields (in a flat long format). If I select a specific value in field (1) I want to know how many  distinct values are connected in each of the other fields.
This would best result in a table with two columns: FieldNames | CountDistinctPossibleSelections

Any idea how to create such table?

[marcus_sommer]

You could create a table-chart with 350 expressions for it, like:

count(distinct Field1)
count(distinct Field2)
...

An alternatively to such a manual approach could be to use $Field as dimension and a string-loop expression like:

$(=concat(TOTAL 'if($Field=' & chr(39) & $Field & chr(39) & ',count(distinct [' & $Field & ']), ') & concat(TOTAL right($Field&')',1)))

but this logic will require a lot of resources and probably not be working with many fields and/or a large dataset - means long calculation times and/or hitting any timeouts or RAM limitations. Beside this the above suggestion used nested if-loops which are limited - in earlier QlikView releases it were 99 nested if-loops, in the current releases I don't know it but I doubt that it were increased rather the opposite. Therefore be careful by applying it. If you really want to go with this and you need more as 99 or n if-loops you need to replace the if-loop with a pick(match()) approach - but it won't be trivial within such string-loop.

Beside this what is the aim for it - for what is this information useful? Further your mentioned 350 fields within a single table indicate that the datamodel might not be optimized - therefore I suggest to check if really all are needed and/or if it's a crosstable which might be better transformed to a stream-table.

- Marcus

[Henke_Chr]

Thanks for you quick reply.

This is a raw data table for a clinical meta-analysis. It has all variables as columns and all observations/data points in rows.

Data for each trial in the file is very heterogenuous. Finally I need to find one unique row per treatment and measured endpoint. Unfortunately, heterogeneity can come from different variables depending on how results are published and trial was designed. 

 Let's say I want compare a specific <endpoint> reported for 20 studies. Out of my 20.000 data points (rows) I should have in the final selection for the analysis 2x 20 rows (1 for each trial arm). I get for example 80 rows by just selcting the <endpoint> in the endpoint variable. Reason for this is that one trail reports 10 different <measurememt timepoints>, the next trial reports 3 different <patient subgoups>, and the next two <value types>, mean and median...

Really hart to figure out in which variables still selection to be made to reach the one row per trial data table for analysis. And I wanted to avoid hard coding as I the load script is setup variable for different variables (each dataset reports different variables/columns).

Hope that was clear enough.

As such a table giving $Field (variable) | number of possible selections (per variable) would be really helpful.

I made a manual approach with a chart table using one variable (like endpoint) as dimension and getpossiblecount(variable n) as expression. showing columns only if getpossiblecount(variable) > 1- but getpossiblecount() does not work with distinct...

...and it needs to be hard coded...

[marcus_sommer]

I'm not sure that I understand your data-structure and what do you want to extract from it but I assume it will be much harder to do it within the UI and not (the most esssential parts) within the script.

The reason for it is your described way means a trial & error approach to select certain values and then to scan the $Field-table for certain match-results - but then, what now doing with it? Using these results for selections and/or any conditions and/or adding further calculations/views on it might be possible but it will be quite difficult - I wouldn't try it.

Like mentioned I think the data needs to be transformed - probably per The-Crosstable-Load - and pre-calculated within the script.

- Marcus

[Henke_Chr]

Thanks Markus, tend to agree that I need to work more on data transformation during load - but wanted to make sure I did not overlook an easy solution that is existing in the UI (and what I was looking for, in theory, is a straightforward report reporting current status of fields that simply is not there in QV).

Christian

[Brett_Bleess]

Christian, have a look at the below link, Rob has quite a few gadgets in his portfolio, not sure if any of them do what you want here, but it is worth having a look I think.  I do not believe the Governance Dashboard has this info either, but that would be the other potential thing to try as well to see what you get there, as I know the app reads some of the meta data, but not sure it is based upon what you want.  Sorry I do not have anything better to add.

https://qlikviewcookbook.com/

Cheers,
Brett

[rwunderlich]

I don't have anything on the website, but I put something together that I think fills @Henke_Chr  request.  You can copy the sheet objects in the attached QVW into any QVW and explore the counts of associated values.  It's kind of fun.

The chart requires that you enable alternate states in your document and add a state named "unlinked". 

I think it's possible to do it without the alternate state but I gave up. 

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com 

[rwunderlich]

Here's an updated version that doesn't require the alternate state. It also adds a percentage gauge and some improved visuals.

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com