topic Re: IntervalMatch with if statement and substring in QlikView

IntervalMatch with if statement and substring

anothergreg — Wed, 25 Nov 2020 16:16:04 GMT

I need to group US medical Procedure Codes based on their ranges. It was easily implemented for CPT codes as they are all 5 byte numeric.

However, it is more difficult with HCPCS codes because they begin with an alpha followed by 4 numeric bytes. The ranges are unique per alpha. I want to use IntervalMatch only when the first byte of the two compare codes are the same, and then perform the function on the last 4 bytes. Example:

R0070-R0076 should be grouped as Diagnostic Radiology Services. I have an excel doc with the ranges set up with:

FromHCPCS, ToHCPCS, HCPCS Group

R0070, R0076, Diagnostic Radiology Services

Load Script below, where Service Cd is the field containing the CPT/HCPCS code

Directory;

HCPCSGrouping:

LOAD FromHCPCS,

ToHCPCS,

[HCPCS Group]

FROM

[..\MISCELLANEOUS_DATA_FILES\CPT HCPC Groups.xlsx]

(ooxml, embedded labels, table is [HCPCS Mapping]);

I just don't know how to force IntervalMatch to only look at the last 4 bytes of my Service Cd, and also within an if statement based on the first byte, then return the original Service Cd to match to the original fact table.

Is this possible with IntervalMatch, or is there another method I should be using?

Re: IntervalMatch with if statement and substring

MarcoWedel — Thu, 19 Oct 2017 21:00:16 GMT

Hi,

as there seems to be only a limited number of possible HCPCS codes one solution might be to expand the Groups to all possible code values e.g. like this:

tabHCPCSGrouping:
LOAD Left(FromHCPCS,1)&Num(From+IterNo()-1,'0000') as HCPCS,
    FromHCPCS,
    ToHCPCS,
    HCPCSGroup
While From+IterNo()-1 <= To;
LOAD @1 as FromHCPCS,
    Mid(@1,2) as From,
    @3 as ToHCPCS,
    Mid(@3,2) as To,
    @5 as HCPCSGroup
FROM [https://www.findacode.com/code-set.php?set=HCPCS] (html, codepage is 1252, no labels, table is @5, filters(Remove(Row, Pos(Top, 1))));

tabFacts:
LOAD Chr(64+Ceil(Rand()*26))&Num(Rand()*10000,'0000') as HCPCS,
    Ceil(Rand()*100) as SomeFact
AutoGenerate 10000;

hope this helps

regards

Marco

Re: IntervalMatch with if statement and substring

effinty2112 — Fri, 20 Oct 2017 08:24:18 GMT

Hi Gregory,

Maybe something like:

HCPCSGrouping:

LOAD

Num(KeepChar(FromHCPCS,'0123456789')) as FromHCPCS,

Num(KeepChar(ToHCPCS,'0123456789')) as ToHCPCS,

[HCPCS Group]

FROM

[..\MISCELLANEOUS_DATA_FILES\CPT HCPC Groups.xlsx]

(ooxml, embedded labels, table is [HCPCS Mapping]);

Then add line like this to your fact load statement:

Num(KeepChar(HCPCS,'0123456789)) as NumHCPCS,

Finally,

Left Join(Facts)

IntervalMatch (NumHCPCS) Load FromHCPCS,ToHCPCS Resident HCPCSGrouping;

God luck

Andrew