topic Re: Inline Table in QlikView

Inline Table

Anonymous — Tue, 22 Aug 2017 18:32:36 GMT

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!

Re: Inline Table

Peter_Cammaert — Tue, 22 Aug 2017 19:09:04 GMT

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

Re: Inline Table

Anonymous — Tue, 22 Aug 2017 19:13:19 GMT

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

Re: Inline Table

Anil_Babu_Samineni — Wed, 23 Aug 2017 04:40:23 GMT

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

Re: Inline Table

balar025 — Wed, 23 Aug 2017 04:47:50 GMT

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

Re: Inline Table

qliksus — Wed, 23 Aug 2017 06:11:19 GMT

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

];