topic Re: Linking 2 Tables With Different Amount Values in QlikView

Linking 2 Tables With Different Amount Values

goro2010 — Thu, 15 Mar 2018 15:22:38 GMT

Good Day,

I hope someone can assist me, I am trying to do the following, as per the graph below, I have two table from 2 different databases pulled into Qlikview, both data sets share the same ID.

What I am trying to achieve, as per below example, I want to sum "Value" - TREE (33) with "CITY" - JHB,PTA,KZN (60,65,100)

Thus the expected answer should be 258.

I have managed to create an external mapping table and doing a left join. The issue is that there is over 80 million records for all these mappings as there is values added like Table 1 (ID = 1,2,4,5,6) and Table 2 (CPT,LMP,WTB) that I won't use.

I just want to find out if there is a better and cleaner way of doing this?

Thank You!

Re: Linking 2 Tables With Different Amount Values

rwunderlich — Thu, 15 Mar 2018 15:29:36 GMT

You can concatenate the two tables together, ideally eliminating the CITY rows you don't need as you load them. Then it becomes as simple as using ID and/or Value for dimension and Sum(Score) as expression.

If you can't eliminate the extra Score rows in the load because you need them for something else in model, you can select the CITY values you want with set analysis.

Sum({<CITY={'JHB','PTA','KZN'}>}Score

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Linking 2 Tables With Different Amount Values

goro2010 — Thu, 15 Mar 2018 15:34:35 GMT

Good Day Rob,

Thank you, ideally I would like to load these concatenate into a new table, I have this challenge "ideally eliminating the CITY rows you don't need as you load them".

Can you please give me a quick example on doing this?

Thank You

Re: Linking 2 Tables With Different Amount Values

rwunderlich — Thu, 15 Mar 2018 15:37:31 GMT

LOAD

ID, CITY, SCORE

FROM whereever

WHERE Match(CITY, 'JHB', 'PTA', 'KZN');

-Rob

Re: Linking 2 Tables With Different Amount Values

goro2010 — Thu, 15 Mar 2018 15:38:38 GMT

Thank you, have not coded for a long time, just forgot the where part - Thank You

Re: Linking 2 Tables With Different Amount Values

goro2010 — Thu, 15 Mar 2018 15:40:53 GMT

Quick question, can you match from a external Excel table and the coulomb index, as the "Match" could rapidly change and I don't want to have it static?

Thank You

Re: Linking 2 Tables With Different Amount Values

rwunderlich — Thu, 15 Mar 2018 15:44:22 GMT

You can use an external source for the match, but in that case you would use Where Exists().

Filters:

LOAD CITY from myspreadsheet...;

Facts:

LOAD

ID, CITY, SCORE

FROM whereever

WHERE Exists(CITY);

DROP TABLE Filters;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Linking 2 Tables With Different Amount Values

goro2010 — Thu, 15 Mar 2018 15:51:49 GMT

BRILLIANT!

Thank You!