topic Re: Ad-Hoc Error reporting logic in QlikView

Ad-Hoc Error reporting logic

NickHoff — Thu, 05 May 2016 15:02:24 GMT

My current error reporting for calculated condition unfulfilled is the following, I commented out the section that I can't get to work:

=IF((GetSelectedCount(@AdHocDim) = 0 AND GetSelectedCount(@AdHocPHIDim) = 0) AND GetSelectedCount(@AdHocMetric) = 0,

'Select a dimension(s) and metric(s) to create a report.',

IF((GetSelectedCount(@AdHocDim) > 0 OR GetSelectedCount(@AdHocPHIDim) > 0) AND GetSelectedCount(@AdHocMetric) = 0,

'Select a metric(s) to create a report.',

/* IF((GetSelectedCount(@AdHocDim) > 0 OR GetSelectedCount(@AdHocPHIDim) > 0) AND GetSelectedCount(@AdHocMetric) = 0 AND GetFieldSelections(@AdHocPHIDim) = 'Patient Account Number',

'Please select a DRG, Surgeon, Procedure, CCS Classification, or a Facility.',/*

'Select a dimension(s) to create a report.'))//)

The logic for the calculated dimension condition and the metric condition is the following:

(GetSelectedCount(@AdHocDim) > 0 OR GetSelectedCount(@AdHocPHIDim) > 0)

AND

GetSelectedCount(@AdHocMetric) > 0

I'm attempting to add the logic for if GetFieldSelections(@AdHocPHIDim) = 'Patient Account Number' it will force the user to select the dimension @AdHocDim = 'DRG' , 'Surgeon', 'Procedure', or 'Facility'

I've attempted logic such as the following but I keep getting a false positive when Facility or Surgeon is selected because there are more than one substringcounts for Facility and Facility Level, or Surgeon Name, Surgeon NPI, Surgeon Group.:

=GetSelectedCount(@AdHocDim) > 0

AND (

(SubStringCount(Concat(@AdHocDim, ','), 'Facility') = 1 AND SubStringCount(Concat(@AdHocDim, ','), 'Facility Level') = 0)

OR SubStringCount(Concat(@AdHocDim, ','), 'Procedure CCS Classification') = 1

OR SubStringCount(Concat(@AdHocDim, ','), 'Product Line Detail') = 1

OR SubStringCount(Concat(@AdHocDim, ','), 'Principal Procedure') = 1

OR SubStringCount(Concat(@AdHocDim, ','), 'Surgeon Name') = 1

OR SubStringCount(Concat(@AdHocDim, ','), 'Surgeon NPI') = 1

OR SubStringCount(Concat(@AdHocDim, ','), 'Surgeon Group') = 1

)

Below is what the current layout looks like all created by inline tables:

Re: Ad-Hoc Error reporting logic

NickHoff — Thu, 05 May 2016 15:12:46 GMT

I'm getting close with the calculated condition unfulfilled as:

=IF((GetSelectedCount(@AdHocDim) = 0 AND GetSelectedCount(@AdHocPHIDim) = 0) AND GetSelectedCount(@AdHocMetric) = 0,

'Select a dimension(s) and metric(s) to create a report.',

IF((GetSelectedCount(@AdHocDim) > 0 OR GetSelectedCount(@AdHocPHIDim) > 0) AND GetSelectedCount(@AdHocMetric) = 0,

'Select a metric(s) to create a report.',

IF(GetFieldSelections(@AdHocPHIDim) = 'Patient Account Number',

'Please select a DRG, Surgeon, Procedure, CCS Classification, or a Facility.',

'Select a dimension(s) to create a report.')))

and the calculated condition as:

(GetSelectedCount(@AdHocDim) > 0 OR GetSelectedCount(@AdHocPHIDim) > 0)

AND

GetSelectedCount(@AdHocMetric) > 0

AND

IF(GetFieldSelections(@AdHocPHIDim) = 'Patient Account Number',GetSelectedCount(@AdHocDim) > 0

AND (

(SubStringCount(Concat(@AdHocDim, ','), 'Facility') = 1 AND SubStringCount(Concat(@AdHocDim, ','), 'Facility Level') = 0)

OR SubStringCount(Concat(@AdHocDim, ','), 'Procedure CCS Classification') = 1

OR SubStringCount(Concat(@AdHocDim, ','), 'Product Line Detail') = 1

OR SubStringCount(Concat(@AdHocDim, ','), 'Principal Procedure') = 1

OR SubStringCount(Concat(@AdHocDim, ','), 'Surgeon Name') = 1

OR SubStringCount(Concat(@AdHocDim, ','), 'Surgeon NPI') = 1

OR SubStringCount(Concat(@AdHocDim, ','), 'Surgeon Group') = 1

))

But i'm still getting false positives when I select more than one @AdHocPHIDim.

Re: Ad-Hoc Error reporting logic

Gysbert_Wassenaar — Fri, 06 May 2016 11:41:14 GMT

If you want exact matches you can try something like this:

SubStringCount(Concat(distinct '|' & @AdHocDim & '|', ','), '|Surgeon|') = 1

That will give a substringcount of 1 only for 'Surgeon', but not for 'Surgeon Name' or 'Surgeon Group' etc.

Re: Ad-Hoc Error reporting logic

swuehl — Fri, 06 May 2016 11:51:58 GMT

Instead of this

OR SubStringCount(Concat(@AdHocDim, ','), 'Procedure CCS Classification') = 1

OR SubStringCount(Concat(@AdHocDim, ','), 'Product Line Detail') = 1

OR SubStringCount(Concat(@AdHocDim, ','), 'Principal Procedure') = 1

OR SubStringCount(Concat(@AdHocDim, ','), 'Surgeon Name') = 1

OR SubStringCount(Concat(@AdHocDim, ','), 'Surgeon NPI') = 1

OR SubStringCount(Concat(@AdHocDim, ','), 'Surgeon Group') = 1

You can also try to use something like

Sum({<@AdHocDim *= {'Procedure CCS Classification','Product Line Detail', 'Principal Procedure','Surgeon Name', 'Surgeon NPI','Surgeon Group'}>} 1)

which should also not return 1 when you select e.g. 'Surgeon' only