Linking 2 Tables With Different Amount Values

[goro2010]

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!

[rwunderlich]

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

[goro2010]

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

[rwunderlich · Accepted solution]

LOAD

     ID, CITY, SCORE

FROM whereever

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

-Rob

[goro2010]

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

[goro2010]

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

[rwunderlich]

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

[goro2010]

BRILLIANT!

Thank You!