Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

richters
New Contributor III

Diagram formula: Finding lowest Distance

Dear all,

I haven't worked with QV for a while and have to develop a new aaplication. Therefor, the application contains two tables:

The Table of OrderCompany contains detail information of orders of chosen companies.

The Table of AllCompanies contains information of every existing company. For every Company, the distance to every place in Germany is in it.

Task:

The QVW should show what happens if a Company or a department of a company (of the Table OrderCompany) is closed.

The Orders has to be moved to the same kind of Department of another Company which is not chosen to be closed (of Table AllCompanies) choosing the lowest Distance.

So: 1 Order has exactly 1 specified "new" Company when OrderCompany or OrderCompanyDepartment is chosen to be closed.

I prepared a qvw example for you. Can you help me to fill in the formulas of the pivot tables. My formulas actually do not work.

Thanks in advance

richters

6 Replies

Re: Diagram formula: Finding lowest Distance

Sorry for (may be) a stupid question... !

How can we know that a company is closed

Can you provide one simple example for any order from your qvw?

richters
New Contributor III

Re: Diagram formula: Finding lowest Distance

In this example there is just one Company which can be closed (OrderCompany). It maybe would make more sense to just close one of the two OrderCompanyDepartments. You can both select them in the green textbox. There, a selection means it is closed.

So you may "close" - or select Department 0200 which contains 2 orders.

Now the tables on the bottom should show, where the order are moved to according to the lowest distance Of course, "closed" companies must not be selected as "new". I may provide a bit more data --> new example here!

Table with red box on the left:

-lowest distance works (is it right?)

-the company does not work (for Order 96288 it has to 521186215 (the lowest distance - company is closed) and for order 96761 company 521101145).

Table on the left:

Has to show these companies with both "1" as new Order in this example.

MVP
MVP

Re: Diagram formula: Finding lowest Distance

For order 96761, isn't the new company #521183053 with a distance of 14,47?

MVP
MVP

Re: Diagram formula: Finding lowest Distance

For department 0100, there seem to be AllCompanies with equal minimum distance. Which one should be chosen then?

edit:

Here is a demonstration of that observation. Multiple Allcompanies with same min distance.

Since I don't really understand how you get to these distances, you'll need to clarify / decide.

richters
New Contributor III

Re: Diagram formula: Finding lowest Distance

Well,

thank you very much! This is exactly what I was searching for. The problem with same minimum distances is a special one which we did not make a decision on it, yet. For instance, could you "just" tell me, how the count (distinct order) formula would look like if we take the statistical approach (one order which has two companies with same distance goes with 0.5 to each of them?)?

That would be great!

Thank you very much!

MVP
MVP

Re: Diagram formula: Finding lowest Distance

That's not trivial.

Maybe like attached....

Community Browser