How to model this?

[Anonymous]

Here is my data

RealEstateAgentID | RealEstateAgentCity

1 | Manhattan

2 | Brooklyn

3 | Brooklyn

and

RealEstateAgentID | HouseId | HouseCity

1 | 1 | Manhattan

1 | 2 | Brooklyn

2 | 3 | Brooklyn

3 | 4 | Brooklyn

The catch is that a given Real Estate Agent can sell houses that are in a city different from where he/she is based

I want to be able to display both those tables in the same Qlikview app :

Table 1 :

                | Nbr of agents | Nbr of houses

Manhattan |        1            |           1

Brooklyn    |        2            |           3

(here notice that the city dimension is not the same "city" with regards to the agents and the house)

and

Table 2:

Agent | Nbr of ads

   1     |      2

   2     |      1

   3     |      1

If I just load the agents and houses tables in a straightforward way (two tables linked by RealEstateAgentID, table 2 is easy of course, but what about table 1? It would yield something like :

                | Nbr of agents | Nbr of houses

Manhattan |        1            |           1

Brooklyn    |        2            |           2 (instead of 3 as the Manhattan house from agent 1 is not counted)

[sinanozdemir]

Hi,

Something like this?

Please see the attached.

Thanks

[Anonymous]

No it doesn't work; looks like my first example happened to make your proposition work, but try with this data (I added the lines in Bold). Your implementation gives only 1 agent in Manhattan instead of 2).

Table1:

LOAD * INLINE [

    RealEstateAgentID, RealEstateAgentCity

    1, Manhattan

    2, Brooklyn

    3, Brooklyn

   4, Manhattan

];

Table2:

LOAD * INLINE [

    RealEstateAgentID, HouseId, HouseCity

    1, 1, Manhattan

    1, 5, Brooklyn

    2, 3, Brooklyn

    3, 4, Brooklyn

    4, 2, Brooklyn

];

[sinanozdemir]

Let's see if this fixes the problem.

Thanks

[Anonymous]

Thanks but this still doesn't work. The nbr of agents is the same on both lines (for instance, add a new line like this

Table1:

LOAD * INLINE [

    RealEstateAgentID, RealEstateAgentCity

    1, Manhattan

    2, Brooklyn

    3, Brooklyn

    4, Manhattan

   5, Newark

];

The result table should be

Manhattan : 2 agents, 1 house

Brooklyn : 2 agents, 4 houses

Newark : 1 agent, 0 house

but with your code it yields

Manhattan : 3 agents, 1 house

Brooklyn : 3 agents, 4 houses

Newark : 3 agents, 0 house

[effinty2112 · Accepted solution]

Hi Oliver,

What about:

Agents:

LOAD * INLINE [

    RealEstateAgentID, RealEstateAgentCity

    1, Manhattan

    2, Brooklyn

    3, Brooklyn

];

Houses:

LOAD * INLINE [

RealEstateAgentID, HouseId, HouseCity

1,1,Manhattan

1,2,Brooklyn

2,3,Brooklyn

3,4,Brooklyn

];

City:

Load Distinct RealEstateAgentCity as City Resident Agents;

Load HouseCity as City Resident Houses Where not Exists(City,HouseCity);

Now we can make these straight tables:

City	Count(DISTINCT if(RealEstateAgentCity = City, RealEstateAgentID))	Count(DISTINCT if(HouseCity = City, HouseId))
Manhattan	1	1
Brooklyn	2	3

RealEstateAgentID	Count(DISTINCT HouseId)
1	2
2	1
3	1

Cheers

Andrew

[Anonymous]

OK this works. So the thing is:

-create a distinct list of town composed of the agent towns and house towns (without duplicates)

-use this list as dimension

-une a simple if statement in the visualisation table

Thanks Andrew.