topic Re: Left Join Duplicated rows in QlikView

Left Join Duplicated rows

Mon, 18 Jul 2016 22:27:11 GMT

Assume I have a tableA:

No	Type	Date	Price
pol01	I	1/1/2013	30000
pol02	II	1/5/2013	36000
pol03	I	4/15/2013	40000
pol04	I	5/7/2013	38000
pol05	III	6/14/2013	37000
pol06	I	7/5/2013	39900
pol07	IV	9/16/2013	42000
pol08	IV	11/2/2013	48000
pol09	I	11/18/2013	41000
pol10	I	12/13/2013	43000

And TableB:

Type	pct1	pct2	pct3	pct4
I	0.4	0.2	0.2	0.2
II	0.3	0.3	0.2	0.2
III	0.3	0.3	0.3	0.1
IV	0.4	0.3	0.2	0.1

I want to get all rows in TableA with all colums in TableB and pct1*Price as pmt1,pct2*Price as pmt2,pct3*Price as pmt3,pct4*Price as pmt4.

before starting the multiplication,

I used

Left Join (TableA)

Load *

Resident TableB

but keep getting duplicated records.

Anyone can help me with it?

Re: Left Join Duplicated rows

Mon, 18 Jul 2016 22:43:41 GMT

when you use resident for read a table B i fink you have on your memory app this table. you read 2 times this table.

try below:

table_a:

No	Type	Date	Price
pol01	I	1/1/2013	30000
pol02	II	1/5/2013	36000
pol03	I	4/15/2013	40000
pol04	I	5/7/2013	38000
pol05	III	6/14/2013	37000
pol06	I	7/5/2013	39900
pol07	IV	9/16/2013	42000
pol08	IV	11/2/2013	48000
pol09	I	11/18/2013	41000
pol10	I	12/13/2013	43000

from source.

left join (table_a)

Type	pct1	pct2	pct3	pct4
I	0.4	0.2	0.2	0.2
II	0.3	0.3	0.2	0.2
III	0.3	0.3	0.3	0.1
IV	0.4	0.3	0.2	0.1

from source:

table:

load *,

pct1*Price as pmt1,

pct2*Price as pmt2,

pct3*Price as pmt3,

pct4*Price as pmt4

resident table_a; drop table table_a;

Re: Left Join Duplicated rows

Mon, 18 Jul 2016 23:27:10 GMT

I still get duplicated records.

Re: Left Join Duplicated rows

sunny_talwar — Mon, 18 Jul 2016 23:37:38 GMT

What is your expected output from the data you have provided above?

Re: Left Join Duplicated rows

Mon, 18 Jul 2016 23:44:52 GMT

I will need a table with following fields:

No, Type, Date, Price, pct1, pct2,pct3,pct4,pmt1,pmt2,pmt3,pmt4

Later, I will need to use 'date' to slice data into smaller groups to calculate.

Thank you.

Re: Left Join Duplicated rows

qlikviewwizard — Tue, 19 Jul 2016 01:34:39 GMT

Hi,

Use this code.

TableA:

LOAD * INLINE [No,Type,Date,Price

pol01,I,1/1/2013,30000

pol02,II,1/5/2013,36000

pol03,I,4/15/2013,40000

pol04,I,5/7/2013,38000

pol05,III,6/14/2013,37000

pol06,I,7/5/2013,39900

pol07,IV,9/16/2013,42000

pol08,IV,11/2/2013,48000

pol09,I,11/18/2013,41000

pol10,I,12/13/2013,43000

];

Left join (TableA)

TableB:

LOAD * INLINE [

Type,pct1,pct2,pct3,pct4

I,0.4,0.2,0.2,0.2

II,0.3,0.3,0.2,0.2

III,0.3,0.3,0.3,0.1

IV,0.4,0.3,0.2,0.1];

Table:

Load *,pct1*Price as pmt1,pct2*Price as pmt2,pct2*Price as pmt3

Resident TableA;

drop Table TableA;