exclude nulls when using Match

[RogerG]

Hello all,

The attachment does not have all the syntax errors.

also note, I used regions and countries as an example, but this is not region/country specific

I have the following

CountryRegion:

MAPPING

load * INLINE [

status, region

LAUNCH_Mexico, LA

FRANCE ,WE

then data

Launch:

LOAD *,

  IF(MATCH(COUNTRY,'LAUNCH_US','LAUNCH_CANADA), 'US', //If it's us or canada, it's US

       IF MATCH(COUNTRY'*LAUNCH*'), 'EX_US', // if it's any other country, it's exus

            APPLYMAP('CountryRegion',COUNTRY,'') // any other values, apply the map or null

       )

) as region

LOAD * INLINE [

COUNTRY

LAUNCH_US

LAUNCH_CANADA

LAUNCH_RUSSIA

HONDURAS

JAPAN, 

FRANCE

]

so the expected output should be

LAUNCH_US, US

LAUNCH_CANADA, US

LAUNCH_RUSSIA, EX_US

HONDURAS, HONDURAS

JAPAN, ''

FRANCE , FRANCE

now the problem

load * 

resident launch

where isnull(region) = 0 // tried len(region) >2 also tried to hide null values in the table itself, with alternate states.

I cannot hide the null value in the table

[Anil_Babu_Samineni]

You mean like this? PFA

[Frank_Hartmann · Accepted solution]

Maybe use Qualify & Unqualify

See attached qvw.

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

//While I made this country specific, it is not
// it was the easiest thing I could think of to demonstrate my issue
CountryRegion:

MAPPING

load * INLINE [
	status, region
	Mexico, LA
	FRANCE ,WE
	JAPAN, JPAC
];

Launch:
LOAD *,
	// these needed to be handled dynamically due to the nubmber of changes month to month, I don't want to keep track of all the items I need to 
	// I know us and canada are always stable
	// all other launch things get EX_US
	//Everything else is defined in the mapping table
  IF(MATCH(COUNTRY,'LAUNCH_US','LAUNCH_CANADA'),'US', //If it's us or canada, it's US
       IF(wildMATCH(COUNTRY,'*LAUNCH*'), 'EX_US', // if it's any other country, it's exus
            APPLYMAP('CountryRegion',COUNTRY,'') // any other values, apply the map or null,
		)

     ) as region;

LOAD * INLINE [
COUNTRY, population
LAUNCH_US, 1000
LAUNCH_CANADA, 2000
LAUNCH_RUSSIA, 3000
HONDURAS, 4000
JAPAN, 5000
FRANCE, 6000
Mexico, 800
];


QUALIFY COUNTRY, region;
regions:
load COUNTRY, region
resident Launch
where region<>'';//DROP Table Launch; // tried len(region) >2 also tried to hide null values in the table itself, with alternate states.

UNQUALIFY COUNTRY, region;

[RogerG]

This defeats the whole purpose.  I don't want to maintain a list of values for any future possibilities.  I mentioned that the region/country is an example, not the real data I am working with.  we have hundreds of values and could be updated anytime.  I was looking to just remove that "France" value from the second table.

[RogerG]

Qualify would work, however I do need to reuse these values back into the original tables.  I was just hoping to not display the nulls.

[Frank_Hartmann · Accepted solution]

And this?

[RogerG]

Perfect, the Null() in the apply map, with all the other null references (where clause and table view) did the trick

Thanks