Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
wuming79
Contributor

Lookup Table or Pivot Table?

Hi,

I have 2 tables in my Qlik Sense App.

Table 1:

FruitsTotal
Apple4
Orange2
Pear6
Banana8
Pineapple2

Table 2:

Fruits CatogoryTasteCountry
AppleGoodA
OrangeVery GoodB
PearNormalC
PineappleYuckD
Jack FruitLousyE
BananaFor MonkeysF
GrapefruitYummyG

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?

2 Replies
Partner
Partner

Re: Lookup Table or Pivot Table?

hi

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]);

sudhakar_budde
Contributor

Re: Lookup Table or Pivot Table?

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/MappingFu...

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

Regards

SB