Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Use APPLYMAP()
there is a video on YouTube
https://www.youtube.com/watch?v=jby-omVFNzI
That's the link
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.
Pretty sure applymap is what you need
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.
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?
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
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
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