Reference data - subquery problem

[#1]

Hello everyone,

I have a problem that I've been struggling with, so I hope you can help me out.

Currently, I have a report that shows up somehow like this:

Passenger	Origin	Destination	Gateway 1	Gateway 2	Direction	(etc)
Michael	KOA	PEK	HNL	ICN	EAST
Peter	SHA	ICN	HNL	(null)	WEST
(etc)

Now, I’ve created a reference table to be able to add extra information to this report. This reference table has information sort of like this:

Origin	Destination	Direction	Class
KOA	HNL	EAST	1
ICN	HNL	EAST	3
(etc)

Basically, what I want to do is, for every passenger in the first report, I want to check all their connections to see if they exist in the reference table. If they do, I want to show the max corresponding class or a sum of their values.

So, for example Michael is traveling from KOA -> HNL -> ICN -> PEK and traveling east, meaning that he would have a class of 1.

On the other hand, Peter wouldn’t have any class, since none of his connections are in the reference table.

Now, I don’t have control of the SQL from the initial report, I can only change the logic in the Qlikview side.

If it was in SQL I could do it using a case and subqueries, but since here all my sources are QVDs I can’t do a subquery using a LOAD.

Any ideas in how I can solve this problem? It would be a HUGE help!

(I'm sort of new to Qlikview, so sorry if somethings aren't 100% accurate)

Thank in advance guys

Guilherme Martins

[#2 · Accepted solution]

Hi Guilherme,

Please have a look at the attached doc.

Let me know if it works!

Thanks,

Fred.

[#3]

Hello Frederic,

That worked like a charm. Thanks for the simplicity in the code!

Thanks,

Guilherme