Left Join Duplicated rows

[#1]

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?

[#2]

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;

[#3]

I still get duplicated records.

[sunny_talwar]

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

[#5]

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.

[qlikviewwizard · Accepted solution]

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;