topic Re: Data model information in QlikView

Data model information

nboccassini — Fri, 18 Apr 2014 09:45:12 GMT

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!

Re: Data model information

alexandros17 — Fri, 18 Apr 2014 10:16:25 GMT

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

Re: Data model information

Fri, 18 Apr 2014 10:25:25 GMT

Example for you:

Re: Data model information

Gysbert_Wassenaar — Fri, 18 Apr 2014 10:33:46 GMT

See attached qvw.

Re: Data model information

nboccassini — Fri, 18 Apr 2014 10:52:57 GMT

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

Re: Data model information

nboccassini — Fri, 18 Apr 2014 11:06:11 GMT

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

Re: Data model information

Gysbert_Wassenaar — Fri, 18 Apr 2014 11:07:32 GMT

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

Re: Data model information

nboccassini — Fri, 18 Apr 2014 11:08:10 GMT

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

Re: Data model information

nboccassini — Fri, 18 Apr 2014 11:10:54 GMT

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

Re: Data model information

Fri, 18 Apr 2014 12:21:19 GMT

Hello,

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

Nik.

Re: Data model information

Fri, 18 Apr 2014 13:01:14 GMT

Try to change chart to pivot table

Re: Data model information

Fri, 18 Apr 2014 13:18:34 GMT

Gabriele,

is Dealr_id from C key to internat_competitor from A?

Re: Data model information

nboccassini — Fri, 18 Apr 2014 13:23:33 GMT

Yes! This is the problem

Re: Re: Data model information

Fri, 18 Apr 2014 15:00:48 GMT

One more idea

I hope, you like it.

Reagrds

Darek

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

Re: Re: Data model information

Fri, 18 Apr 2014 16:08:10 GMT

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