Do I use sets, IF statement, or something else?

[#1]

Background:

I play a game called 'Elite:Dangerous' which involves flying around space, trading between space stations (and some shooting)

I thought I'd try to expand my knowledge of Qlikview by putting the data I have on some of the star systems and space stations in the game.

I have two lists of star systems (System of Origin and System) - these are essentially the same list, but with different field names so I can look at 'where I am' and 'where I'm going to'

Each System also has a Station, and at each Station there is a Item list, with association Buy and Sell values.

Each system also has an X/POZ,Y/POY,Z/POZ coordinate (for System or System of Origin accordingly).

I calculate the distance between system1 and system2 using their respective xyz coordinate:

=sqrt(((X-POX)*(X-POX))+((Y-POY)*(Y-POY))+((Z-POZ)*(Z-POZ)))

I have added a field called Range which simply contains a value.

What I want to display is all the Systems within xRange of my System of Origin.

How do I do this?

I've tried sets and I've tried If statements, both locked up qlikview and my computer.

The CSVs are exported from another program, and this is the format they come out in, so I'm unwilling to change these (as the data is updated regularly)- I have attached the relevant csvs, and also my loadscript (I am aware of the typo in the loadscript loading one field as aStation instead of Station - I have corrected this in my qlikview.).

[NOTE]

I'm on the personal edition so can't open any qlickview files you attach.

Thanks in advance for your help and advice.

Rob

[Peter_Cammaert · Accepted solution]

If you created two System tables (System of Origin & System of Destination) without any interconnection, then you are probably trying to calculate the big Cartesian Product with that expression. What you can do is this:

• Create a listbox with the name of the System Of Origin
• Create a Straight Table with a conditional show that hides the table if there is more than one selected/possible value in the listbox System Of Origin. Use an expression like GetSelectedCount([unq:name]) = 1
• In the straight table, select System Of Destination (name) as only dimension.
• Add an expression that calculates: =IF(sqrt(...) <=:$(vRange), sqrt()). Replace sqrt() with your distance calculation expression, and vRange with the name of the variable that contains your range value

Best,

Peter

[#3]

Exactly what was after.

Thank you!