Advanced key logic

Thu, 29 Oct 2015 13:15:18 GMT

So I'm one step from finalizing a new application and of course I've gotten stuck.

The problem is that I'm trying to tag a transaction to data from another table but I can't for the life of me figure out how to design the key.

Lets say that this is my input data:

load * inline

[

GeoCountry, GeoState, GeoCity, GeoImportantData

US, CA, Los Angeles, 44455

US, CA, , 99933

US, MA, Boston, 55588

US, MA, , 88811

]

;

load * inline

[

Country, State, City, CustomerID

US, CA, Los Angeles, 1

US, CA, San Francisco, 2

US, CA, San Jose, 3

US, MA, Boston, 4

US, MA, Springfield, 5

]

Now, I want to tag "GeoImportantData" to each customer. The problem is that all customers have a city whereas only some cities have unique "ImportantData".

A person from LA needs to get value "44455" added but his brother in SF should get "99933". I'm sure this is ridiculously simple but I'm completely stuck in a loop (metaphorical, not database-wise...)

Creating a key from "GeoCountry & GeoState" would mean that a person from CA would get two possible ImportantDatas, creating one from GeoCountry & GeoState & GeoCity would mean that a person from a city that does not have its own unique ImportantData is null.

I desperately need your help in figuring out how to solve this.

Thank you in advance!

topic Advanced key logic in QlikView

Advanced key logic