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

Execute Set Analysis stored in a table

Greetings.

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

KeyValueSetAnalysis
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?

2 Replies
johnw
Not applicable

Re: Execute Set Analysis stored in a table

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;

Not applicable

Re: Execute Set Analysis stored in a table

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.