topic Re: Lookup function???? in QlikView

Lookup function????

Tue, 02 Jul 2013 22:12:27 GMT

Hi,

I have a table 1 with 3 fields:

Product code

Description

Location

Table 2 with a number of fields:

Product code

Balance

Cost

Etc......

Both tables are linked by product code but there are product codes in table 1 with locations that don't exist in table 2. What I am trying to do is lookup product code from table 1 to see if they exist in table 2.

I have looked into the lookup function but don't really understand how it works or if it will provide what I am looking to achieve.

Is there a function that will allow me to this, if exists?

Regards,

Gregg

Re: Lookup function????

rustyfishbones — Tue, 02 Jul 2013 22:18:54 GMT

Use APPLYMAP()

there is a video on YouTube

Re: Lookup function????

rustyfishbones — Tue, 02 Jul 2013 22:21:42 GMT

https://www.youtube.com/watch?v=jby-omVFNzI

That's the link

Re: Lookup function????

Tue, 02 Jul 2013 22:37:06 GMT

I don't want to have to maintain mapping tables I had a look at using apply map but because I need to look in two tables I'm not sure this will work.

I need to return a location in table 1 and in table 2 return the product code if it exists else return null.

Re: Lookup function????

rustyfishbones — Tue, 02 Jul 2013 22:43:13 GMT

Pretty sure applymap is what you need

Re: Lookup function????

Wed, 03 Jul 2013 08:19:26 GMT

I am not mapping anything, so applymap is not needed, I am just trying to lookup information in another table.

All the information is there because I have set the link up through product code, I can get around it another way but I thought there would be a function to make it easier.

Re: Lookup function????

Wed, 03 Jul 2013 08:40:13 GMT

Hi,

You can use Exists instead of lookup in this case. See below example:

Table1:
Load * inline [
productCode, Description, Location
1001, aaaa, Mumbai
1002, bbbb, Mumbai
1003, cccc, Mumbai
1004, dddd, Pune
1005, eeee, Pune
1006, ffff, Pune
1007, gggg, mas
1008, hhhh, mas
];

Table2:
Load * inline [
productCode, Balance, Cost
1001, 100,101
1002, 200, 202
1003, 300, 303
1004, 400, 404
1017, 700, 707
] Where Exists(productCode);

Is this make sence?

Re: Lookup function????

hic — Wed, 03 Jul 2013 08:48:12 GMT

If you just want to check whether the value exists in Table2, you should use the Exists() function. For example:

Table2:

Load *,

[Product code] as [Table2.Product code]

From Table2;

Table1:

Load *,

If(Exists([Table2.Product code],[Product code]),'Exists', 'Does not exist') as CodeExists

From Table1;

You need to check against a field that only exists in Table2, otherwise the Exists() function will pick up previous values in Table1 (if there are duplicates).

HIC

Re: Lookup function????

Wed, 03 Jul 2013 10:15:23 GMT

Hi Henric,

I am looking for products that have a location in table 1 and dont exist in table 2.

Does this mean I have to break the product code link so they all wont associate and load?

Also, if I use If(Exists([Table2.Product code],[Product code]),'Exists', 'Does not exist') as CodeExists

I have a table 1 with 3 fields:

Product code

Description

Location

Table 2 with a number of fields:

Product code

Balance

Cost

When you say I need to check against a field that only exists in table 2, does that mean I have to check against fieldname balance?

Regards,

Gregg

Re: Lookup function????

hic — Wed, 03 Jul 2013 11:50:59 GMT

If you just want a way to find products that have no Balance (or Cost), but still have a Location, you should probably not hard-code the query in the script. Instead, in the GUI, just select all Balances and then select excluded in the Product code list box. If you have products in table 1 that lack location, you should as a third step select all Locations.

See more in this blog post http://community.qlik.com/blogs/qlikviewdesignblog/2013/05/02/finding-null

If you want this property as a field, you need to define a field in the script in the way I described earlier.

Or you can use applymap to create a field "Has Location":

HasLocation:

Mapping

Load [Product code], If(Len(Trim(Location))>0,True(),False()) as HasLocation

From Table1 ;

Table1:

Load * From Table1;

Table2:

Load *,

ApplyMap('HasLocation',[Product code],False()) as HasLocation

From Table2;

Under no circumstances should you break the product code link.

HIC