Lookup query

[j_fulcher]

Hi All,

I am experimenting with Lookup() in the load script of an app i'm working on and have an issue. My data has this form:

Year	ID	PeerID	Sales	Peer Sales
2010	1_1	1_1	110	110
2010	1_2	1_2	320	320
2010	1_3	1_3	250	250
2010	2_1	1_1	600	110
2011	1_1	1_1	540	540
2011	1_2	1_2	130	130
2011	1_3	1_3	270	270
2011	2_1	1_1	820	540
2011	3_1	1_1	560	540

The red column is the field I am hoping to produce in Qlik.

I have used the following:

lookup('Sales','ID',PeerID) as [PeerSales]

Unfortunately this always looks for the first value with the correct value of ID and therefore does not always lookup data in the same accident year. Is there a way to only look for values within a specified range of the data, i.e. only in the correct 'Year'?

Thanks in advance for any help

[sunny_talwar · Accepted solution]

Try this

Table:
LOAD RowNo() as RowNum,
	 *;
LOAD * INLINE [
    Year, ID, PeerID, Sales
    2010, 1_1, 1_1, 110
    2010, 1_2, 1_2, 320
    2010, 1_3, 1_3, 250
    2010, 2_1, 1_1, 600
    2011, 1_1, 1_1, 540
    2011, 1_2, 1_2, 130
    2011, 1_3, 1_3, 270
    2011, 2_1, 1_1, 820
    2011, 3_1, 1_1, 560
];

Left Join (Table)
LOAD Year,
	 ID as PeerID,
	 Sales as PeerSales
Resident Table;

[j_fulcher]

Thanks, this has done exactly what I needed!