topic Re: Execute Set Analysis stored in a table in QlikView

Execute Set Analysis stored in a table

Fri, 26 Aug 2011 19:50:00 GMT

Greetings.

I have a series of complex set analys that need to be executed based on certain values in a table. For example:

KeyValue	SetAnalysis
MU1	<DenomMeasureID={"MU1"}, OnsetDate={">=$(vPeriodStart)<=$(vPeriodEnd)"}>
MU2	<DenomMeasureID={"MU2"}, OnsetDate={">=$(vPeriodStart)<=$(=DATE('$(vPeriodEnd)' -4))"}, DenomType={"REQUESTEDPHI"}>
MU3	<NPCDate={">=$(vDenomStart)<=$(vPeriodEnd)"}, NPCDenomType={"Encounter"}, NPCDenomValue={$(vMU3EMCodes)} >

I can dump the SetAnalysis value in a chart, but if I tried to do the following code, it gives me null or the entire set of IDs. For example, the Dimension in the chart is KeyValue, and an Expression in the chart is:

COUNT( DISTINCT { $(SetAnalysis) } PatientID)

If I replace the Expression with this:

COUNT( DISTINCT { <DenomMeasureID={"MU1"}, OnsetDate={">=$(vPeriodStart)<=$(vPeriodEnd)"}> } PatientID)

It works fine (well... it give the same value for all rows, which is not what I'm trying to accomplish).

I have tried a number of variations:

COUNT( DISTINCT { $(=SetAnalysis) } PatientID)

COUNT( DISTINCT { SetAnalysis } PatientID)

And nothing gives me what I want. What am I doing wrong?

Re: Execute Set Analysis stored in a table

johnw — Fri, 26 Aug 2011 20:11:27 GMT

Dollar sign expansion is done up front, outside of the context of your chart dimensions. At that time, unless you've selected a single KeyValue, SetAnalysis has multiple values, and therefore returns null. So then you probably just count the distinct PatientIDs, ignoring the set you wanted.

If you avoid the dollar sign expansion by just sticking the field in there directly, it will have a unique value for SetAnalysis. However, at that point, it will be a string, not something actually inserted into the code to be evaluated. In other words, I think it would be the equivalent of this:

COUNT( DISTINCT {'<DenomMeasureID={"MU1"}, OnsetDate={">=$(vPeriodStart)<=$(vPeriodEnd)"}>'} PatientID)

Where the single quotes make this a completely invalid expression, and so I would expect it to return null. You just can't use field values this way.

The evaluate() function would be perfect, except that it's explicitly disallowed in expressions.

The best way I've found to get around these limitations (and I'm not saying it's a good way) is to generate a pick() expression that can then choose the correct expression to apply. See attached example. Script for the expression part and variable generation below.

Metric:
LOAD recno() as MetricSequence,* INLINE [
Metric, Expression
Count, count(Amount)
Max, max(Amount)
Total, sum(Amount)
];

Metrics:
LOAD concat(Expression,',',MetricSequence) as AllExpressions
RESIDENT Metric;

LET vMetrics = 'pick(MetricSequence,' & peek('AllExpressions') & ')';

DROP TABLE Metrics;

Re: Execute Set Analysis stored in a table

Mon, 29 Aug 2011 12:58:45 GMT

Thank you for the explaination on the order of execution. Now I understand why I'm getting those results in my chart.

I'm not sure if the pick idea will work for what I'm doing, but I will keep that in mind.