How to create an inline table from an existing table

I have a table in qlik Sense loaded from the database.

Example:

From this I need to make a filter that will display all the Fruit/Veg records along with records from other joined tables, when selected.

The filter needs to be something like this :

|FRUIT_XXX|
|VEG_XXX  |

Any help will be appreciated.

Not sure if its possible to be dynamic. Usually I solve these by creating a new field that combines the values from both fields into one field

RawData:
Load * Inline [
ID , FRUIT   ,VEG     , COUNT
1  , Apple   ,        , 5
2  , Figs    ,        , 10
3  ,         ,Carrots , 20
4  , Oranges ,        , 12
5  ,         ,Corn    , 10
];

Combined:
Load
  ID,
  'FRUIT_' & FRUIT as Combined
Resident
  RawData
Where
  FRUIT <> ''
;

Concatenate

Load
  ID,
  'VEG_' & VEG as Combined
Resident
  RawData
Where
  VEG <> ''  
;

This will create new table (Combined) which will be linked to the main table by ID field:

The new Combined field will have the values like this:

And the UI:

P.S. If further processing is needed you can join the Combined table to the RawData table. This way the Combined field will become part of the RawData table. To achieve this just extend the script a bit:

join (RawData)
Load * Resident Combined;

Drop Table Combined;

I do not know how to do it in qlicksense, but in SQL it's like this:

SELECT 
   ID
   CASE WHEN FRUIT IS NULL THEN VEG ELSE FRUIT END as FruitOrVeg,
   COUNT
FROM tablename

To do this dynamically, you should use the CrossTable() function. This will allow you to pivot your table such that n number of columns "pivot" into just 2 columns, one for the column name and one for the column value.

Here's the script you could use to achieve this:

// STEP 1

[Original]:
Load * Inline [
ID	,	FRUIT	,	VEG		,	COUNT
1	,	Apple	,			,	5
2	,	Figs	,			,	10
3	,			,	Carrots	,	20
4	,	Oranges	,			,	12
5	,			,	Corn	,	10
];

// STEP 2.

Rename Field [ID] to [ID old];
Rename Field [COUNT] to [COUNT old];

[Reordered]:
NoConcatenate Load
    [ID old] as [ID]
  , [COUNT old] as [COUNT]
  , *
Resident [Original];

Drop Table [Original];
Drop Fields [ID old], [COUNT old];

// STEP 3.

[Dynamic]:
CrossTable ([Type], [Item], 2)
Load *
Resident [Reordered];

Drop Table [Reordered];

// STEP 4.

[Combined]:
Load
    [ID]
  , [COUNT]
  , [Type]
  , [Item]
  , [Type] & '_' & [Item] as [Full Item]
Resident [Dynamic]
  Where Not IsNull(EmptyIsNull([Item]))
;

Drop Table [Dynamic];

Here's how it works:

STEP 1.: Load the data from Oracle DB. You can add new food categories as columns as needed.

STEP 2.: We are assuming that we may dynamically load in new food categories, but not other types of new columns like "Price" or things like that. In order to get the dynamic part to work in Qlik, we will use the CrossTable() function in the next step. That function requires fields to be in order of "regular" fields that stay put, followed by "food category" fields that will be pivoted. As such, we are going to ensure that our two "regular" columns are ordered first. To achieve this, we simply rename them temporarily using the Rename Field keywords and then loading them first in our [Reordered] table, making sure that we alias them back to their original field names. This allows us to use the asterisk *, which will load all other fields after those ones. This makes it so that you can load as many food category fields as you want in the future -- we'll always load the them after our "regular" fields.

STEP 3.: Here we use the CrossTable ([Type], [Item], 2) function, which is saying "after the first 2 fields, pivot the remaining fields such that the column names ("FRUIT", "VEG") go into a newly generated [Type] field and all of the column values ("Apple", "Figs", "Carrots", "Corn") go into another newly generated field called [Item].

STEP 4.: Finally, we can combine our [Type] and [Item] fields into a new [Full Item] field to get the result you desire. We use the ampersand & operator to concatenate the fields values together. Our Where clause, Where Not IsNull(EmptyIsNull([Item])), is saying "return records where [Item] is not null, and empty values are to be considered null in this case."

This is what your result should look like:

Then if you wanted to add new food category fields in the future, like dairy, your load script is fully dynamic and will correctly handle those new records and columns:

[Original Plus Dairy]:
Load * Inline [
ID	,	FRUIT	,	VEG		,	DAIRY	,	COUNT
1	,	Apple	,			,			,	5
2	,	Figs	,			,			,	10
3	,			,	Carrots	,			,	20
4	,	Oranges	,			,			,	12
5	,			,	Corn	,			,	10
6	,			,			,	Milk	,	31
7	,			,			,	Cheese	,	19
8	,			,			,	Yogurt	,	13
];

Also, if you still want to keep your original [FRUIT] and [VEG] fields, you can just delete or comment out the Drop Table [Reordered]; line from my example script above.