Inline Table

[Anonymous]

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!

[Peter_Cammaert · Accepted solution]

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

[Anonymous]

Where would i put this load statement? At the end of the inline?  Or where i previously loaded the City field?

[Anil_Babu_Samineni]

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

[balar025]

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

[qliksus]

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

  ];