Array in Array filter Record Selection 1

[RustyAfro]

Hello,

I need to do a filter (after the database query has run). The formula field is a comma delimited string that I want to filter against a parameter array.

The selection formula below fails with "This array must be subscripted. For example: Array."

AND SPLIT({@FILTER_Products},",") IN({?dbSUB_ICD9})

This tells me the left side of the IN needs to be a single string value to compare IN the {?dbSUB_ICD9} array.

However, I need to filter for each row in the report where any of that row's {@FILTER_Products} comma delimited field values match at least one of the {?dbSUB_ICD9} array values.

In other words, this would sort of mimic a SQL EXISTS statement but at the crystal (not SQL) level with array against array.

Can this be done in the Record Selection area?

 

[lbass]

What is the content of your nested formula?

-LB

 

[RustyAfro]

Not sure if I understand, but the fields contain data like below.

ICD9 is actually numerical, so I would probably have to convert products to number somehow.

{@FILTER_Products}: "79.3,45.5,56.2"
{?dbSUB_ICD9}: 45.5,55.5,32.5

 

[lbass]

Please show the formula itself.

-LB

 

[RustyAfro]

Oh, it's not a nested formula. That's an excerpt from the Record Selection (just an AND <evaluation>). My Record Selection is probably 3 pages long and works ok, but this specific filter is the one I'm having trouble with. If I remove all other Record Selection evaluations and just use this one the same problem applies.

I have been working on the problem and so far have created this Formula Field below named {@FILTER_ICD9s}, which works pretty fast in the report (shows true or false). It would be nice if there was an "Exit DO" or something to terminate the loop when the first match is found (as the rest is unnecessary for that row):

whilereadingrecords;
stringvar array ICD9_Array := split({UWVW_BKB436_PROC_COV.THREE_DIG_PROC_CD_ALL}, ",");
numbervar Counter;
booleanvar isFound:=False;
for counter := 1 to ubound(ICD9_Array) do(
if ICD9_Array[counter] in ({?dbSUB_ICD9}) then
isFound:=true;
);
isFound

Then in the Record Selection I tried the below, but, it just hangs for some reason. Which is strange cause the formula field parses quickly whilereadingrecords so not sure why a filter would cause the slowdown. Any ideas?

(IF {?dbSUB_ICD9} = "ALL" Then 
TRUE
ELSE
{@FILTER_ICD9s} = TRUE)

 

[RustyAfro]

Hmm, actually throwing in "Exit For" parses in the DO loop. I assume the below then will exit the Do Loop when isFound = true as I have specified (seems to work ok in tests). Still the slowness issue in the Record Selection.

whilereadingrecords;
stringvar array ICD9_Array := split({UWVW_BKB436_PROC_COV.THREE_DIG_PROC_CD_ALL}, ",");
numbervar Counter;
booleanvar isFound:=False;
for counter := 1 to ubound(ICD9_Array) do(
if ICD9_Array[counter] in ({?dbSUB_ICD9}) then
isFound:=true;
IF isFound:=true Then exit for 
);
isFound

 

[RustyAfro]

Ack, nevermind, that "IF isFound:=true Then exit for " makes everything true. not sure how to exit so leaving that alone for now.

 

[RustyAfro]

The freezing occurs with the message "Accessing Database". It doesn't make sense to me because the "{@FILTER_ICD9s} = TRUE)" should be a local filter after the database query (right?). Without the filter the formula field works fine and the query executes in a seconds.

 

[RustyAfro]

Ok, have something working sort of. If put the code in the Group Selection, the freeze up does not occur on "Accessing Database" and it does more or less work. My report totals unfortunately stay the same, I need this filter to work in Report Selection to really display the results correctly. However, this is something for now. Lbass if you find anytime to give me a theory I would appreciate it .

(IF {?dbSUB_ICD9} = "ALL" Then
TRUE
ELSE
{@FILTER_ICD9s} = TRUE)

Oh, and here is the code for the Exit For that works:

whilereadingrecords;
stringvar array ICD9_Array := split({UWVW_BKB436_PROC_COV.THREE_DIG_PROC_CD_ALL}, ",");

numbervar Counter;
booleanvar isFound:=False;

for counter := 1 to ubound(ICD9_Array) Do
(
if ICD9_Array[counter] in ({?dbSUB_ICD9}) then isFound:=true;
If isFound=True then Exit For;
);

 

[lbass]

A record selection clause using variables will not pass to the SQL query, so during the Accessing Database phase, only other clauses are being applied as filters, and then the clause containing the variable is being applied locally. If you add it to the group selection formula instead, the clause is only being applied to the subset of records already returned by your record selection formula.

Are you saying that the records displayed are incorrect (i.e., that they include records that should have been filtered out by the group selection) or only that calculations based on the displayed records are incorrect? If the latter, then the problem is that you need to use running totals since the more usual inserted summaries will include non-group selected records. If you are seeing records that should have been filtered out, then the formula isn't working.

-LB

 

[RustyAfro]

Hi LB,

In the group selection, the records are filtering out properly, but some totals I created still calculate the filtered out rows. Changing to running totals would do the trick, but it would be a lot of work (this report is already highly complex).

Your description of the record selection is exactly my understanding of how it should work. The SQL filters first, then this new local one should filter rows brought in by the SQL last. But for some reason, crystal hangs at "accessing database" when I put the filter in the Record Selection. I have mixed SQL and local filters before in Record Selection and have never experienced this freezing before. I pasted the entire Record Selection below. The local filter is the last AND. Any other ideas why the Record Selection having that local filter would cause the holdup at Accessing Database?

ISNULL({UWTBL_MAPPING_RMC_PLAN_ALIAS1.MEDPLAN})

AND

(
IF {?db_Facility_Name} = "ALL" Then 
TRUE
ELSE
{SVC300_PRV_DIMSN.SVC_PRV_FULL_NM} IN {?db_Facility_Name}
)

AND

(
IF {?db_NetworkType} = "ALL" Then 
TRUE
ELSE
{%CLM_NetworkType} = {?db_NetworkType}
)

AND

(
IF {?db_CPT_Codes} = "ALL" Then 
TRUE
ELSE
{%CPT_CODE} IN {?db_CPT_Codes}
)

AND

(
IF {?db_SUBS_SSN} = "ALL" Then 
TRUE
ELSE
{MBR300_MBR_DIMSN.SUB_SSN} = {?db_SUBS_SSN}
)



AND 

 (   
    {PLH300_PH_DIMSN.PH_NBR} = {?db_PH_GRP_Identifiers}
  OR
    {PLH300_PH_DIMSN.PH_CAT_ID} = {?db_PH_GRP_Identifiers}
 )


AND 
 (
    {MDC301_EVT_ATTR_DIMSN.CLM_CATEG_CD}      IN ['3', '5', 'B', 'E', 'F', 'I', 'P', 'T', 'V', 'W', 'Y']
  OR
    {MDC301_EVT_ATTR_DIMSN.CLM_CATEG_CD}       IN ['M', 'S']
   AND 
       NOT ({MDC301_EVT_ATTR_DIMSN.CLM_RSK_TYP_CD} IN ['AD', 'AR', 'DD', 'DR']))
   AND ({MDC301_EVT_ATTR_DIMSN.CLM_ATOS_CD} startswith '11' 
    OR {MDC301_EVT_ATTR_DIMSN.CLM_ATOS_CD} startswith '12' 
    OR {MDC301_EVT_ATTR_DIMSN.CLM_ATOS_CD} startswith '311' 
    OR {MDC301_EVT_ATTR_DIMSN.CLM_ATOS_CD} startswith '312' 
    OR {MDC301_EVT_ATTR_DIMSN.CLM_ATOS_CD} startswith '159'
 )

AND 
//This pushes down to server
( IF {?cr_Report} = 3 THEN {%GRP_ATOS_Report3} <> "Not Coded"
  ELSE IF {?cr_Report} = 5 THEN {%GRP_ATOS_Report5} <> "Not Coded"
)          

AND {%GRP_Product} IN {?db_Products}

AND 

IF {?Display Flat File (Current Period Only)} = TRUE THEN
        {TME300_DLY_TM_DIMSN_Paid.CLNDR_MTHLY_DT} = {?db_YearMo_Current}
ELSE IF {?Display Flat File (Prior Period Only)} = TRUE THEN
        {TME300_DLY_TM_DIMSN_Paid.CLNDR_MTHLY_DT} = {?db_YearMo_Prior}
ELSE
     (  
        {TME300_DLY_TM_DIMSN_Paid.CLNDR_MTHLY_DT} = {?db_YearMo_Prior}
      OR
        {TME300_DLY_TM_DIMSN_Paid.CLNDR_MTHLY_DT} = {?db_YearMo_Current}
     )

AND

(
IF {?dbSUB_ICD9} = "ALL" Then 
TRUE
ELSE
{@FILTER_ICD9s} = TRUE
)

 

[lbass]

I believe the last clause is still being applied to all data, but locally, not just that which is filtered by the first part of the formula. Only if you use group selection is the criterion being applied just to that data filtered by the record selection formula.

You MUST use running totals if you use group selection.

I also think you should be setting off your if/then statements in parens, and add some parens to set off the "or" clauses a little more clearly. I can't really tell what criteria you intend to "go with" what in the following sections:

AND
(
{MDC301_EVT_ATTR_DIMSN.CLM_CATEG_CD} IN ['3', '5', 'B', 'E', 'F', 'I', 'P', 'T', 'V', 'W', 'Y']
OR
{MDC301_EVT_ATTR_DIMSN.CLM_CATEG_CD} IN ['M', 'S']
AND
NOT ({MDC301_EVT_ATTR_DIMSN.CLM_RSK_TYP_CD} IN ['AD', 'AR', 'DD', 'DR']))
AND ({MDC301_EVT_ATTR_DIMSN.CLM_ATOS_CD} startswith '11'
OR {MDC301_EVT_ATTR_DIMSN.CLM_ATOS_CD} startswith '12'
OR {MDC301_EVT_ATTR_DIMSN.CLM_ATOS_CD} startswith '311'
OR {MDC301_EVT_ATTR_DIMSN.CLM_ATOS_CD} startswith '312'
OR {MDC301_EVT_ATTR_DIMSN.CLM_ATOS_CD} startswith '159'
)

...

AND

IF {?Display Flat File (Current Period Only)} = TRUE THEN
{TME300_DLY_TM_DIMSN_Paid.CLNDR_MTHLY_DT} = {?db_YearMo_Current}
ELSE IF {?Display Flat File (Prior Period Only)} = TRUE THEN
{TME300_DLY_TM_DIMSN_Paid.CLNDR_MTHLY_DT} = {?db_YearMo_Prior}
ELSE
(
{TME300_DLY_TM_DIMSN_Paid.CLNDR_MTHLY_DT} = {?db_YearMo_Prior}
OR
{TME300_DLY_TM_DIMSN_Paid.CLNDR_MTHLY_DT} = {?db_YearMo_Current}
)

AND

(
IF {?dbSUB_ICD9} = "ALL" Then
TRUE
ELSE
{@FILTER_ICD9s} = TRUE
)

-LB

 

[WalterHeisenberg]

Hello, I have hopefully a quick question. I am trying to do a simple:

where CUSTID not in ( select CUSTID from blah)

in crystal and can't seem to get this working. No matter what I try I just can't get it. Basically I am left joining two tables which share a primary key and figuring out which ids from table A are not in table b.

I saw the In Array function and thought that might work but not in or = false did not work. Any tips would greatly be appreciated. Thanks in advance.

Regards,

Ariel

 

[lbass]

I think you could just use a left join FROM table A TO Table B and then use a record selection formula like this:

isnull({tableB.primarykey})

-LB

 

[WalterHeisenberg]

Thanks for the help LB - it did work.

Sorry for hijacking the thread. I meant to post the question in it's own thread and entered it in the wrong window .