Combine three table into one

[prma7799]

Dear All,

I have three table in the attachment but I dint understand that How can i create these three table into one table.

I want to find out that which therapy have which brand etc.

PFA...

Thanks

[MayilVahanan]

PFA..

Are you looking like this? All brands comes under each therapy..

Or only few brand comes under each Therapy. If so, what is the logic?

[#3]

Hi qlikview7799,

You see your Item qvd, it is repeating Item_Code based on the different values in each columns.

Eg,

Item_Code	Dept_code	Div_code	Brd_code	N_Brand	N_Dept	N_Division	Seg_code	N_Thearpy
WEDDTL000	1650				Exports - Dossiers
WEDDTL000		2180				United Kingdom
WEDDTL000			7152	Desloratadine Group
WEDDTL000							1170	Exports-Dossiers

You can Convert multiple rows into one row like below.

Item_Code	Dept_code	Div_code	Brd_code	N_Brand	N_Dept	N_Division	Seg_code	N_Thearpy
WEDDTL000	1650	2180	7152	Desloratadine Group	Exports - Dossiers	United Kingdom	1170	Exports-Dossiers

after carefully observing the table, we can ignore the joining of Brand and Thearpy qvd as both table details is present in Item Table.

Brd_Code - Brand Code, N_Brand - Brand Name , Seg_Code - Thearpy code and N_Thearpy is Thearpy Name.

You can have a look into below code for your further reference and combining three table to one.

BrandName:

LOAD Brand_Code,

     Brand_Name

FROM

Bnd.qvd

(qvd);

Right Join

LOAD Item_Code,

     MAX(Brd_code) As Brand_Code,

     MaxString(N_Brand) AS N_Brand,

     MAX(Dept_code) As Dept_code,

     MaxString(N_Dept) As N_Dept,

     MAX(Seg_code) As Seg_code,

     MaxString(N_Thearpy) As N_Thearpy,

     MAX(Div_code) As Div_code,

     MaxString(N_Division) As N_Division

FROM

ITE.qvd

(qvd)

GROUP By Item_Code;

Left Join

LOAD Therapy_Code,

     Therapy_Name As N_Thearpy    // We Don't Have Code in Ite Qvd

FROM

They.qvd

(qvd);

Thanks

[prma7799]

Hi Mayil,

As you see all therapy will show same brands.

thanks

[prma7799]

Your solution seems ok I haven't check yet. But I want to put ITE table as main because ITE table is link with my existing data model.

Thanks

[prma7799]

Hi Nandkishor / All , sunindia‌, Avinashelite‌, tresesco‌

I have implement your above solution but It is working fine. But I am one - line and some sale shows in it.

How to solve it....

Thanks