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!

[alexandros17]

Non so se ho ben compreso il problema ma potresti utilizzare nel caricamento dei dati un alias cioè quando carichi:

Select

...

Price as PriceB

...

From Table B

[#3]

Example for you:

[Gysbert_Wassenaar]

See attached qvw.

[nboccassini]

This solution works, but it duplicate data and for my project it's equal to 3-4Gb of data plus the original data

[nboccassini]

This solution don't duplicate data but I can't see in each row the price for dealer and its competitor

[Gysbert_Wassenaar]

Are you sure you didn't overlook the drop table statement? There's only one resulting table after the entire script has run.

[nboccassini]

Ma questo significherebbe duplicarmi una marea di dati che nel mio caso significherebbe aggiungere altri 3-4 Gb di dati.

[nboccassini]

I know it, but for example, the price for the DEALER 4 (INTERNAL) is in the column PRICE and PRICE_DEALER

[#10]

Hello,

if the table A is small then you can try the method from example in attach.
I wish it helps.

Nik.