Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
pbrenneise
New Contributor

Qlik Sense Straight Table Charts Expressions

Hello,

I am fairly new to Qlik Sense and am attempting to build a straight table chart using columns from several tables pulled from a vendor's API. Until I can refine the data load editor script to create a clean, standalone table from these tables, I am relying on visualizations to draw connections between these tables.

The original table in the vendor system looks like this:

ID ColumnNameYearOwnerCountry
1test2014SteveBrazil
1test2014MarySweden
2testing 1 22016SteveBrazil
3test Test2019JimCanada

 

Once retrieved via the REST connector, each of the above columns are separated by Qlik into several tables linked via the ID column. As a quick example, each resulting table follows a structure like this:

[Name]:

ID ColumnName
1test
2testing 1 2
3test Test

 

[Year]:

ID ColumnYear
12014
22016
32019

 

[Owner]:

ID ColumnOwner
1Steve
1Mary
2Steve
3Jim

 

[Country]:

ID ColumnCountry
1Brazil
1Sweden
2Australia
3Canada

 

When building visualizations, this works well in pivot tables, KPIs, bar charts, etc., but Qlik ends up creating too many rows if adding each of these columns into a straight table chart since some tables are larger than others. The result looks like the following (see red for the bad results), which is basically adding new rows for every match between ID Column and the Country column in the [Country] table:

ID ColumnNameYearOwnerCountry
1test2014SteveBrazil
1test2014SteveSweden
1test2014MaryBrazil
1test2014MarySweden
2testing 1 22016Steve Brazil
3test Test2019JimCanada

 

Is there an expression that I can add to the sheet or chart to filter out these unintended rows? Thanks!

Labels (1)
1 Solution

Accepted Solutions
srishsum2017
Contributor

Re: Qlik Sense Straight Table Charts Expressions

Try APPLYMAP for country mapping, because APPLYMAP will pick very first record for each ID Column.

Sumit Kumar Srivastava

View solution in original post

2 Replies
srishsum2017
Contributor

Re: Qlik Sense Straight Table Charts Expressions

Try APPLYMAP for country mapping, because APPLYMAP will pick very first record for each ID Column.

Sumit Kumar Srivastava

View solution in original post

pbrenneise
New Contributor

Re: Qlik Sense Straight Table Charts Expressions

I was utilizing ApplyMap incorrectly in my first go at it. I tweaked how I was using it and got the needed results. Thanks!