topic Re: join inventory and sales workbooks in QlikView

join inventory and sales workbooks

vikas_nandanwar — Mon, 13 Nov 2017 10:22:52 GMT

Hi,

I have one inventory excel with Date, A, B, C, D, E columns,

Sales excel with Date, C, F, G,L columns,

How do i link both excel in one?

Thanks,

Vikas

Re: join inventory and sales workbooks

Anil_Babu_Samineni — Mon, 13 Nov 2017 10:28:39 GMT

Please go through Booklet or Workplace of reference sheet.

Ans - Simply rename it and use Join for 2 tables

Re: join inventory and sales workbooks

vikas_nandanwar — Mon, 13 Nov 2017 11:53:01 GMT

please elaborate

Re: join inventory and sales workbooks

Anil_Babu_Samineni — Mon, 13 Nov 2017 11:55:49 GMT

Here we go

Load Date, A, B, C, D, E from Excel1;

Join

Load Date, C as A, F as B, G as C, L as D from Excel2;

Re: join inventory and sales workbooks

neha_shirsath — Mon, 13 Nov 2017 12:01:02 GMT

The data level of both excel is same?

Means the fields A,B,C,D,E and C,F,G,L has same data? if yes, then you can simply rename and join both.

Re: join inventory and sales workbooks

shraddha_g — Mon, 13 Nov 2017 12:01:59 GMT

Try

Load Date &'-'& C as Key,

Date as Inventory_Date, A, B, C as Inventory_C, D, E

From Inventory;

Join

Load Date &'-'& C as Key,

Date as Sales_Date, C as Sales_C, F, G,L

From Sales;

Re: join inventory and sales workbooks

vikas_nandanwar — Wed, 22 Nov 2017 04:06:07 GMT

No Data in A, B, C, D, E, F, G, L has different data.

Only Date column and Column C has same data

Re: join inventory and sales workbooks

neha_shirsath — Wed, 22 Nov 2017 07:34:41 GMT

Try this-

Inventory1:

LOAD * Inline [

Date , A, B, C, D, E ];

Sales1:

LOAD * Inline [

Date, C , F, G,L

];

Inventory:

Load Date &'-'& C as Key1,

Date , A, B, C, D, E Resident Inventory1; drop Table Inventory1;

Sales:

Load Date &'-'& C as Key2,

Date, C , F, G,L Resident Sales1; DROP Table Sales1;

LinkTable:

Load Date &'-'& C as Key1,

Date as LinkDate,

C as LinkC

resident Inventory;

concatenate

Load Date &'-'& C as Key2,

Date as LinkDate,

C as LinkC

resident Sales;

Drop Fields Date, C From Inventory;

Drop Fields Date, C From Sales;

Re: join inventory and sales workbooks

vikas_nandanwar — Fri, 24 Nov 2017 09:32:26 GMT

no this doesn't work

Re: join inventory and sales workbooks

shraddha_g — Fri, 24 Nov 2017 10:06:37 GMT

can you share sample data?

Re: join inventory and sales workbooks

vikas_nandanwar — Fri, 24 Nov 2017 10:14:41 GMT

Common columns in both sheets - Date, Co. Code , Type

Sheet 1 Name - Stock - Work book Name Metal Stock

Date	Dept	WorkCenter	Sub Dept	Qty	Co. Code	Type
2-Jan-17	Mfg	A	PURE	10	ZZ	Gold
2-Jan-17	RM	B	KARAT	30	XX	Platinum
2-Jan-17	Mfg	C	PURE	30	YY	Silver
2-Jan-17	JW	A	SPRUE	20	ZZ	Platinum
2-Jan-17	Mfg	D	FIL	40	XX	Gold
2-Jan-17	RM	D	BAG	50	YY	Silver
3-Jan-17	Mfg	A	CHK	10	WW	Platinum
3-Jan-17	JW	B	FIL	20	VV	Gold
3-Jan-17	Mfg	C	FILWIP	20	WW	Gold
3-Jan-17	JW	B	CHK	10	VV	Silver
3-Jan-17	RM	C	FIL	30	XX	Platinum
3-Jan-17	RM	B	BAG	30	ZZ	Platinum

Sheet 2 - Name Sales - Workbook name Metal Sales

Co. Code	Date	Entry Date	Particulars	Sales	Type
321	7-Jan-17		EXH160037	5	Gold
321	7-Jan-17		JXP161761	5	Silver
321	9-Jan-17		JXP161767	50	Platinum
321	9-Jan-17		JXP161768	45	Platinum
321	9-Jan-17		JXP161770	5	Silver
321	9-Jan-17		JXP161771	10	Gold
321	10-Jan-17		JXP161788	5	Gold
321	10-Jan-17		JXP161791	150	Silver
321	12-Jan-17		JXP161794	5	Platinum
321	12-Jan-17		JXP161795	5	Silver
321	12-Jan-17		JXP161797	5	Gold
321	12-Jan-17		JXP161804	275	Platinum
321	13-Jan-17		JXP161805	185	Silver
321	13-Jan-17		JXP161808	85	Platinum