topic Re: Combine three table into one in QlikView

Combine three table into one

prma7799 — Mon, 28 Mar 2016 09:29:58 GMT

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

Re: Combine three table into one

Chanty4u — Mon, 28 Mar 2016 09:41:46 GMT

load all 2 qvds and replce below code

Thearpy:

LOAD Therapy_Code &' _'& '_' &Brand_Name & Brd_code as Newcode,

Therapy_Name

FROM

DIMQVTherapy.qvd

(qvd);

Re: Combine three table into one

prma7799 — Mon, 28 Mar 2016 09:53:28 GMT

code is not work....

Re: Combine three table into one

Chanty4u — Mon, 28 Mar 2016 09:59:54 GMT

can u share ur third qvd...

bcz ur attached qvd has duplicate

can chk and attach 3qvds

Re: Combine three table into one

prma7799 — Mon, 28 Mar 2016 10:03:04 GMT

tresesco‌,Avinashelite‌, jaganmohanrao, sunindia‌,

Please look into this.

Re: Combine three table into one

prma7799 — Mon, 28 Mar 2016 10:04:05 GMT

Please check above.

Re: Combine three table into one

MayilVahanan — Mon, 28 Mar 2016 11:34:42 GMT

From your data, there is brd_code is null for N_therapy.

N_Thearpy	Brd_code
Alimentry Systems
Allopathy
Analgesics & Antipyretics
Anti-Haemorrhagics
Anti-Helminthic
Anti-Infective
Anti-Malarials
Anti-Obesity
Antidiabetic
Cardiovascular System
Dental
Dermatology
Exports-Dossiers
Exports-Regulated
Gynaecology
Hormones
Musculo-Skeletal Disorders
Neurology
Nutrition
Oncology
Opthalmic/Ent
Respiratory Systems & Anti-Allergy
Urinary

What is the expected output? N_Brand and Brand_Name has same data.. What is the purpose of "Bnd" qvd. Could you able to explain in details

Re: Combine three table into one

Chanty4u — Mon, 28 Mar 2016 11:39:39 GMT

I think there no field values Brd_code,

Please chk it and update

Re: Combine three table into one

prma7799 — Mon, 28 Mar 2016 11:45:15 GMT

Hi MayilVahanan‌

Thanks for your reply ....

In the attached I have one pivot table I want output like that whenever I take dimension as Therapy I want show all brands respect to that therapy.

I know there is no any relation between Therapy n brand but I have to do association with them.

Please do the needful.

Re: Combine three table into one

florentina_doga — Mon, 28 Mar 2016 11:55:51 GMT

try this

ITEM:

LOAD Item_Code,

Brd_code,

N_Brand,

Dept_code,

N_Dept,

Seg_code,

N_Thearpy,

Div_code,

N_Division

FROM

ITEMDIMENSIONS.qvd

(qvd);

left join (ITEM)

LOAD Brand_Code as Brd_code,

Brand_Name

FROM

DIMQVBrand.qvd

(qvd);

is not necesary table Thearpy.

Re: Combine three table into one

MayilVahanan — Mon, 28 Mar 2016 11:59:17 GMT

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?

Re: Combine three table into one

Mon, 28 Mar 2016 12:16:27 GMT

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

Re: Combine three table into one

prma7799 — Mon, 28 Mar 2016 12:26:29 GMT

Hi Mayil,

As you see all therapy will show same brands.

thanks

Re: Combine three table into one

prma7799 — Mon, 28 Mar 2016 12:42:56 GMT

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

Re: Combine three table into one

prma7799 — Tue, 29 Mar 2016 09:46:20 GMT

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