Data model information

[nboccassini]

Hi,

I need help for modeling data in qlikview. I have these table:

Table A contains information of the relation of dealer (of my company) and the competitor of other company (external competitor) and competitor in my company (internal competitor)

TABLE A:
DEALER_ID	INTERNAL_COMPETITOR	EXTERNAL_COMPETITOR_ID
1	4	2
2	3	7
3	4	2
4		3
…	…	…

Table B contains prices of one product for the external competitor

TABLE B:
EXTERNAL_COMPETITOR_ID	DATE	PRICE
1	01/04/2014	2,45
2	01/04/2014	6,34
3	01/04/2014	35,34
4	01/04/2014	34,46
5	01/04/2014	2,56
6	01/04/2014	64,1
7	01/04/2014	45,45
…	…	…

Table C contains prices for the dealer of my company (so DEALER_ID and INTERNAL_COMPETITOR):

TABLE C:
DEALER_ID	DATE	PRICE
1	01/04/2014	223,64
2	01/04/2014	64,2
3	01/04/2014	7,33
4	01/04/2014	34,88
5	01/04/2014	21,03
6	01/04/2014	44,88
7	01/04/2014	14,21
…	…	…

I want to realize a report like this:

DEALER_ID	COMPETITOR	COMPANY	DATA	PRICE DEALER	PRICE COMPETITOR
1	4	INTERNAL	01/04/2014	223,64	34,46
1	2	EXTERNAL	01/04/2014	223,64	64,2
2	3	INTERNAL	01/04/2014	64,2	7,33
2	7	EXTERNAL	01/04/2014	64,2	45,45
3	4	INTERNAL	01/04/2014	…	…
3	2	EXTERNAL	01/04/2014	…	…
4	3	EXTERNAL	01/04/2014	…	…
…	…	…	…	…	…

The problem is the internal competitor, because prices for these dealer that I want in the column PRICE COMPETITOR are in the same table A of the PRICE DEALER.

How can I model the data? I know that I can duplicate the information for the internal competitor and put it in the TABLE B, but I want to avoid this solution because the real tables are big (4GB Data).

Thanks!

[#2]

Try to change chart to pivot table

[#3]

Gabriele,

is Dealr_id from C key to internat_competitor from A?

[nboccassini]

Yes! This is the problem

[#5]

One more idea

I hope, you like it.

Reagrds

Darek

ps. changing to v2 with small (distinct) improvement.

[#6]

I hope, last one version

In v2 i forgot about COMPETITOR_ID

And we must remember, that especioally in the case of bigger data volumes in memory usage cache has bigger part.

Qlikview stores data compressed (distinct values + binary index). Sometimes it is better to join tables. Shorter way from table to table means less time to propagate selections.

regards

Darek

ps. I changed to v4