topic Re: How to model this? in QlikView

How to model this?

Anonymous — Tue, 12 Jul 2016 16:48:35 GMT

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)

Re: How to model this?

sinanozdemir — Tue, 12 Jul 2016 17:09:26 GMT

Hi,

Something like this?

Please see the attached.

Thanks

Re: How to model this?

Anonymous — Tue, 12 Jul 2016 17:33:20 GMT

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

];

Re: How to model this?

sinanozdemir — Tue, 12 Jul 2016 17:44:49 GMT

Let's see if this fixes the problem.

Thanks

Re: How to model this?

Anonymous — Wed, 13 Jul 2016 09:49:36 GMT

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

Re: How to model this?

effinty2112 — Wed, 13 Jul 2016 10:57:28 GMT

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

Re: How to model this?

Anonymous — Wed, 13 Jul 2016 11:56:59 GMT

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.