topic Vlookup like function in loadscript in QlikView

Vlookup like function in loadscript

Fri, 09 Dec 2011 15:10:59 GMT

Hi,

I would like to add a number of fields to a table, without expanding the number of rows. Therefore I was thinking about a way to use a lookup function, which would give me the field values of another table corresponding with a field vale from my current table.

Table1:

FieldA	FieldB	FieldC	FieldPC
11	abc	0020	33
22	def	0060	34
23	ght	0020	35
24	gyr	0070	40

Table2:

ID	GB	WS	PC
1	010	5	0020
2	020	8	0020
3		9	0030
4		10	0050

Suppose I would like to join the values of of Table2 to Table1 based on PC. Although two rows have the same value for PC in Table2, I only want to add the first row (based on ID or load order).

I tried lookup(), left join and mapping load, but I have not made it work unfortunately.

Vlookup like function in loadscript

Fri, 09 Dec 2011 15:19:55 GMT

Hi there, I am pretty sure you can accomplish this using maps or joins, the trick is to load only the usable rows from the second table, with a code similar to the following:

UsableIDs:

Load PC, min(ID) as MinID

from Table2.qvd (qvd)

group by PC;

Table2:

load ID, GB, WS, PC

from Table2.qvd (qvd)

where exists(MinID, ID);

Regards

Vlookup like function in loadscript

Sat, 10 Dec 2011 14:22:31 GMT

Thanks, this will do the trick!