Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cbaqir
Specialist II
Specialist II

Load Script: Where with nulls and not match

Can someone help me fix my WHERE statement? I want the NULLs to come in categorized as Not Classified and I want to exclude 'Facility/Regional Owned Plan' and 'Nursing and Ancillary'.

REVIEW:
LOAD VERSION_PW_CAT_ID,
DESCRIPTION,
GOVERNING_CCG,
date( LAST_REVIEW_DATE, 'MM/DD/YYYY') as LAST_REVIEW_DATE,
DOMAIN,
autonumber(VERSION_PW_CAT_ID & '|' & DOMAIN) as %PP_DOMAIN_Key //Join to PP_USAGE
   // EXTRACT_DATE
FROM
[..\External_Data\ACTIVE PLANS 06192017.xlsx]
(
ooxml, embedded labels, table is Sheet1);
where GOVERNING_CCG is null or GOVERNING_CCG not in ('Facility/Regional Owned Plan','Nursing and Ancillary');

1 Solution

Accepted Solutions
vishsaggi
Champion III
Champion III

Check this may be:

REVIEW:

LOAD VERSION_PW_CAT_ID,

DESCRIPTION,

IF(ISNULL(GOVERNING_CCG) = -1, 'Not Classified', GOVERNING_CCG) AS GOVERNINGCCG,

date( LAST_REVIEW_DATE, 'MM/DD/YYYY') as LAST_REVIEW_DATE,

DOMAIN,

autonumber(VERSION_PW_CAT_ID & '|' & DOMAIN) as %PP_DOMAIN_Key //Join to PP_USAGE

   // EXTRACT_DATE

FROM

[..\External_Data\ACTIVE PLANS 06192017.xlsx]

(ooxml, embedded labels, table is Sheet1)

where Len(Trim(GOVERNING_CCG)) = 0 OR NOT MATCH(GOVERNING_CCG, 'Facility/Regional Owned Plan','Nursing and Ancillary');

View solution in original post

1 Reply
vishsaggi
Champion III
Champion III

Check this may be:

REVIEW:

LOAD VERSION_PW_CAT_ID,

DESCRIPTION,

IF(ISNULL(GOVERNING_CCG) = -1, 'Not Classified', GOVERNING_CCG) AS GOVERNINGCCG,

date( LAST_REVIEW_DATE, 'MM/DD/YYYY') as LAST_REVIEW_DATE,

DOMAIN,

autonumber(VERSION_PW_CAT_ID & '|' & DOMAIN) as %PP_DOMAIN_Key //Join to PP_USAGE

   // EXTRACT_DATE

FROM

[..\External_Data\ACTIVE PLANS 06192017.xlsx]

(ooxml, embedded labels, table is Sheet1)

where Len(Trim(GOVERNING_CCG)) = 0 OR NOT MATCH(GOVERNING_CCG, 'Facility/Regional Owned Plan','Nursing and Ancillary');