Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
@@Following is sample the price data
Material Code | Customer Location | Max SFT DFC | Safety Days Supply | Min SFT DFC | W48-17 | W49-17 | W50-17 | W51-17 | W52-17 |
AN13101 | P01 | 116 | 2 | 1 | 0 | 0 | 0 | 0 | 0 |
DK15111 | P02 | 71 | 43 | 22 | 70 | 101 | 96 | 91 | 86 |
DK15111 | P03 | 71 | 43 | 22 | 0 | 0 | 0 | 0 | 0 |
FT11124 | P02 | 59 | 30 | 15 | 122 | 117 | 112 | 107 | 102 |
FT11124 | P03 | 59 | 30 | 15 | 145 | 140 | 135 | 130 | 125 |
Following is sample volume data
Material Code | Customer Location | Max SFT DFC | Safety Days Supply | Min SFT DFC | W48-17 | W49-17 | W50-17 | W51-17 | W52-17 |
AN13101 | P01 | 116 | 2 | 1 | 0 | 0 | 0 | 0 | 0 |
DK15111 | P02 | 71 | 43 | 22 | 6,850 | 8,660 | 8,160 | 7,588 | 6,969 |
DK15111 | P03 | 71 | 43 | 22 | 0 | 0 | 0 | 0 | 0 |
FT11124 | P02 | 59 | 30 | 15 | 2,930 | 2,728 | 2,551 | 2,349 | 2,130 |
FT11124 | P03 | 59 | 30 | 15 | 2,170 | 2,038 | 1,923 | 1,791 | 1,649 |
I need to create a relation ship between these two tables, in qlik by multipling each week price with each week volume. this infomration refreshes every week as it is rolling weeks data.
thank you!
Join both table
Before joining rename W48-17 column as W48-17_price in Price table
and W48-17_Volume in volume table
do same for other column also
Regards,
Thank you for your response Prashant! Sure will defiantly qualify and unqualify the data. How ever, what is the best way to join the tables and write script for each column to do the price*volume? is this some thing you could help. That will simplify my work as this is going to be a weekly task for me to finish am looking for easy and simply script
I suggest to transform your data at first per The Crosstable Load. After them I would check if this calculation needs to be done really on script-level? If not, I would just concatenate both tables and perform the calculation within the charts.
Instead of the concatenation you could of course do a joining or mapping but both methods will be more expensive especially if there multiple key-values in one of tables or that one or both tables have not all key-values.
- Marcus
thank you! as per your suggestion i concatenated bot the cross tables, but in the out put, could you please help me with why volume number are not populating?
Please check if you named the value-field within the crosstable-statement differently and if those values are really not existing within your data.
- Marcus
It would be sthing like this :
Price:
load "Material Code","Custmer Location","Max SFT", "Safety Days Supply", "Min SFT DFC", "W48-17" as "W4817 Price", W49-17 as "W49-17 Price", W50-17" as "50-17" Price", W52-17" as "W52-17 Price" from sourcePrice;
join(Price)
load "Material Code","Custmer Location","Max SFT", "Safety Days Supply", "Min SFT DFC", "W48-17" as "W4817 Volume", W49-17 as "W49-17 Volume", W50-17" as "50-17" Volume", W52-17" as "W52-17 Volume" from sourceVoume;
final0:
noconcatenate:
load "Material Code","Custmer Location","Max SFT", "Safety Days Supply", "Min SFT DFC",
"W4817 Volume"*"W4817 Price" as W4817 ,
"W49-17 Volume"*"W49-17 Price" as W4917,
"50-17 Volume" * "50-17 Price" as W5017,
"W52-17 Volume" *"W52-17 Price" as W5217
RESIDENT Price;
final:
noconcatenate
crosstable (Week,Volume*Price,5) load *;
load "Material Code","Custmer Location","Max SFT", "Safety Days Supply", "Min SFT DFC",
W4817 ,
W49-17,
W50-17,
W52-17
resident final0;
final1:
noconcatenate:
load "Material Code","Custmer Location","Max SFT", "Safety Days Supply", "Min SFT DFC",
"W4817 Volume" as "W4817",
"W49-17 Volume" as W4917,
"50-17" Volume" as W5017,
"W52-17 Volume" as W5217
resident Price;
lef join (final)
crosstable (Week,Volume,5) load *;
load "Material Code","Custmer Location","Max SFT", "Safety Days Supply", "Min SFT DFC",
W4817 ,
W49-17,
W50-17,
W52-17
resident final1;
final2:
noconcatenate:
load "Material Code","Custmer Location","Max SFT", "Safety Days Supply", "Min SFT DFC",
"W4817 Price"as W4817 ,
"W49-17 Price" as W4917,
"50-17" Volume" AS W5017,
"W52-17 Price" AS W5217
resident Price;
lef join (final)
crosstable (Week,Volume,5) load *;
load "Material Code","Custmer Location","Max SFT", "Safety Days Supply", "Min SFT DFC",
W4817 ,
W49-17,
W50-17,
W52-17
resident final2;
drop tables Price, final0, final1, final2;
Thank you for the inputs. i did work the way you said. What if i use qualify over doing it manually the naming convention.