Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
Example for you:
See attached qvw.
This solution works, but it duplicate data and for my project it's equal to 3-4Gb of data plus the original data
This solution don't duplicate data but I can't see in each row the price for dealer and its competitor
Are you sure you didn't overlook the drop table statement? There's only one resulting table after the entire script has run.
Ma questo significherebbe duplicarmi una marea di dati che nel mio caso significherebbe aggiungere altri 3-4 Gb di dati.
I know it, but for example, the price for the DEALER 4 (INTERNAL) is in the column PRICE and PRICE_DEALER
Hello,
if the table A is small then you can try the method from example in attach.
I wish it helps.
Nik.