Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 Column | Name | Year | Owner | Country |
1 | test | 2014 | Steve | Brazil |
1 | test | 2014 | Mary | Sweden |
2 | testing 1 2 | 2016 | Steve | Brazil |
3 | test Test | 2019 | Jim | Canada |
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 Column | Name |
1 | test |
2 | testing 1 2 |
3 | test Test |
[Year]:
ID Column | Year |
1 | 2014 |
2 | 2016 |
3 | 2019 |
[Owner]:
ID Column | Owner |
1 | Steve |
1 | Mary |
2 | Steve |
3 | Jim |
[Country]:
ID Column | Country |
1 | Brazil |
1 | Sweden |
2 | Australia |
3 | Canada |
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 Column | Name | Year | Owner | Country |
1 | test | 2014 | Steve | Brazil |
1 | test | 2014 | Steve | Sweden |
1 | test | 2014 | Mary | Brazil |
1 | test | 2014 | Mary | Sweden |
2 | testing 1 2 | 2016 | Steve | Brazil |
3 | test Test | 2019 | Jim | Canada |
Is there an expression that I can add to the sheet or chart to filter out these unintended rows? Thanks!
Try APPLYMAP for country mapping, because APPLYMAP will pick very first record for each ID Column.
Try APPLYMAP for country mapping, because APPLYMAP will pick very first record for each ID Column.