topic Re: create a relation ship between these two tables in QlikView

create a relation ship between these two tables

bushpalaamarnat — Wed, 25 Nov 2020 16:16:04 GMT

@@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!

Re: create a relation ship between these two tables

PrashantSangle — Wed, 29 Nov 2017 13:10:14 GMT

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,

Re: create a relation ship between these two tables

bushpalaamarnat — Wed, 29 Nov 2017 14:07:30 GMT

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

Re: create a relation ship between these two tables

marcus_sommer — Wed, 29 Nov 2017 16:52:10 GMT

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

Re: create a relation ship between these two tables

bushpalaamarnat — Fri, 01 Dec 2017 13:01:29 GMT

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?

Re: create a relation ship between these two tables

marcus_sommer — Sat, 02 Dec 2017 09:54:36 GMT

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

Re: create a relation ship between these two tables

OmarBenSalem — Sat, 02 Dec 2017 12:23:41 GMT

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;

Re: create a relation ship between these two tables

bushpalaamarnat — Tue, 12 Dec 2017 16:07:01 GMT

Thank you for the inputs. i did work the way you said. What if i use qualify over doing it manually the naming convention.