topic Re: Lookup Table or Pivot Table? in App Development

Lookup Table or Pivot Table?

wuming79 — Thu, 18 Oct 2018 08:07:48 GMT

Hi,

I have 2 tables in my Qlik Sense App.

Table 1:

Fruits	Total
Apple	4
Orange	2
Pear	6
Banana	8
Pineapple	2

Table 2:

Fruits Catogory	Taste	Country
Apple	Good	A
Orange	Very Good	B
Pear	Normal	C
Pineapple	Yuck	D
Jack Fruit	Lousy	E
Banana	For Monkeys	F
Grapefruit	Yummy	G

In my App, I like to show Table 1 as a bar chart but I only want to show Fruits that were reflected in Table 1 in Table 2. How should I do it? Lookup or pivot?

Re: Lookup Table or Pivot Table?

lironbaram — Thu, 18 Oct 2018 08:42:14 GMT

first i would give the fruits column the same field name so the tables will be connected ,

if in the front end you want to display only fruits with amount

two approaches :

1.add a measure to table two sum(Total), and in adds-on properties uncheck display zero values

2. create a calculated dimension with this expression : aggr(if(sum(Total)>0,Fruits),Fruits) then make sure you uncheck display zero value in the dimension.

there is another approach to this via script

your script should look something like :

Table1:

load * from XXX;

Table2:

load * from YYY

where exists(Fruits,[Fruits Category]);

Re: Lookup Table or Pivot Table?

sudhakar_budde — Thu, 18 Oct 2018 08:46:27 GMT

Hi Lewis,

One way you can achieve this is, you can load the Table 1 as mapping table and add 'Total' to the Table 2 using applymap() function. Where there is no 'Total' for any 'Fruit category' there you can assign 0 as default 'Total' or Null().

Then you can plot a chart with 'Fruits Category' as dimension and 'Total' as measure. Then you need to suppress zero values or Null values from the fields. If you use 0 as default value, then on the chart properties--> add ons--> handle data -->Uncheck include zero values. If use Null( ) as default in applymap() then on the Dimension field -->uncheck 'Include null values.

Note: when loading the mapping table (table 1) make sure to change the name of 'Fruits' field to 'Fruits Category' so that the apply map works.

Refer the link for applymap() fucntion:

https://help.qlik.com/en-US/sense/September2018/Subsystems/Hub/Content/Sense_Hub/Scripting/MappingFunctions/ApplyMap.htm

Hope this helps. Can you please mark this answer appropriately if helped.

Regards