topic exclude nulls when using Match in QlikView

exclude nulls when using Match

RogerG — Wed, 06 Jan 2021 02:07:09 GMT

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

Re: exclude nulls when using Match

Anil_Babu_Samineni — Wed, 06 Jan 2021 08:41:41 GMT

You mean like this? PFA

Re: exclude nulls when using Match

Frank_Hartmann — Wed, 06 Jan 2021 10:44:46 GMT

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;

Re: exclude nulls when using Match

RogerG — Wed, 06 Jan 2021 14:25:01 GMT

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.

Re: exclude nulls when using Match

RogerG — Wed, 06 Jan 2021 14:25:55 GMT

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.

Re: exclude nulls when using Match

Frank_Hartmann — Wed, 06 Jan 2021 14:53:29 GMT

And this?

Re: exclude nulls when using Match

RogerG — Wed, 06 Jan 2021 18:27:57 GMT

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

Thanks