Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
cbaqirdhds
Valued Contributor

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');

Tags (4)
1 Solution

Accepted Solutions
vishsaggi
Esteemed Contributor III

Re: Load Script: Where with nulls and not match

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');

1 Reply
vishsaggi
Esteemed Contributor III

Re: Load Script: Where with nulls and not match

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');