Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
And this?
You mean like this? PFA
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;
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.
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.
And this?
Perfect, the Null() in the apply map, with all the other null references (where clause and table view) did the trick
Thanks