Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a data set that has multiple cities, that are grouped in to Northtowns, Southtowns City and Other. This data set will continue to grow as new people register their address and people from outside of the area (who do not fit in to these categories) will enter new items in the "Other" category. Is there a way to make an inline table like the one below?
TBL_Area:
LOAD * INLINE
[ City, AreaName
CityX, City
CityY, Northtown
CityA, Northtown
CityB, Southtown
CityC, Southtown
CityD, Southtown
Other, Other ];
There will not be a value of "Other" in the data set, but is there a way to have the inline table group all other values in to a single category?
Thank you!
Well, there are multiple ways to tackle this problem of extensibility. One could be to use an applymap() call with a default value. This can be done in a number of scenarios. The simplest one is detailed below.
Create a mapping table with the known and categorised cities. You can use your example and simply add the Mapping keyword in front. Convert their names to upper case. Do not include an 'Other' category.
Load your source data into a resident table. It should include a field called City. Expand your LOAD statement with a piece of code like this:
LOAD ...
:
ApplyMap('TBL_Area', Upper(City), 'Other') AS AreaName,
:
FROM ...;
Your table will now contain an AreaName for all known cities. The others will have the default AreaName 'Other'.
Peter
Well, there are multiple ways to tackle this problem of extensibility. One could be to use an applymap() call with a default value. This can be done in a number of scenarios. The simplest one is detailed below.
Create a mapping table with the known and categorised cities. You can use your example and simply add the Mapping keyword in front. Convert their names to upper case. Do not include an 'Other' category.
Load your source data into a resident table. It should include a field called City. Expand your LOAD statement with a piece of code like this:
LOAD ...
:
ApplyMap('TBL_Area', Upper(City), 'Other') AS AreaName,
:
FROM ...;
Your table will now contain an AreaName for all known cities. The others will have the default AreaName 'Other'.
Peter
Where would i put this load statement? At the end of the inline? Or where i previously loaded the City field?
his intention is, You could need Mapping table each row and then use the Applymap() in help. then create others which is not required they are allocated to OTHERS. That is how his intention
Hi Chris,
You need to create one mapping table of City and Area.
TBL_Area:
Mapping LOAD * INLINE
[ City, AreaName
CityX, City
CityY, Northtown
CityA, Northtown
CityB, Southtown
CityC, Southtown
CityD, Southtown];
And use applymap inside your load script of fact/dimension. and put 'other' as default value.
ApplyMap('TBL_AREA',City_Field,'Other') as Area
Regards,
Ravi
Maybe something like the below
TBL_Area:
LOAD if( SubField('Rest,Other',',')='Other','Other', City) as City, AreaName , City as City1 INLINE
[ City, AreaName
CityX, City
CityY, Northtown
CityA, Northtown
CityB, Southtown
CityC, Southtown
CityD, Southtown
];