topic Re: Look Up Function in QlikView

Apply Map Function (Not an Inline Load - 2 fields)

Anonymous — Mon, 16 Mar 2015 18:14:21 GMT

Hey Community,

So here is the scenario:

I am developing App "New Clientele" and a list of client IDs. I have a previous app called "Business History." The client wants to use client IDs from the Business History app as a look up against client IDs from the "New Clientele" app so the business can have a yes or no flag if clients in the "New Clientele" already have a order history in the "Business History" app. So all in all, I need some type of look up function for two sets of data to make a yes or no flag.

So essentially I need a flag (Y/N) for duplicates in two different fields not Inline.

Thanks a lot,

Rocco

Re: Look Up Function

Anonymous — Mon, 16 Mar 2015 18:19:14 GMT

Sounds like a candidate for a Mapping Load and the ApplyMap() function.

This Technical Brief Joins and Lookups explains ApplyMaps(), plus some other options.

Re: Look Up Function

chrismarlow — Mon, 16 Mar 2015 18:23:50 GMT

You can't (as far as I am aware) look up directly from one QVW to another (as you might be able to in a relational database, even something like MS Access) after it has been loaded.

An option (presuming you still have control over it) is when your "Business History" application is executed it could include an export to a QVD of the data you need (you would need to put into a table first, then drop the table). You then pick that data up as a source QVD for "New Clientele" application, populating your yes/no flag.

You are creating a dependency between the two applications which you would need to make transparent and if that is a problem you could go back to the source of "Business History" data and work out from that what it means to be included in the final model with the and incorporate that into "New Clientele" (if it is straight forward).

Re: Look Up Function

maxgro — Mon, 16 Mar 2015 18:23:56 GMT

if you can modify Business History, at the end add a NewTable; pseudo code

NewTable:

load clientid, count(orderhistory) as countorderhistory

resident orderehistorytable

group by clientid;

store NewTable in qvd

drop newtable

in New Clientele load the qvd

Re: Look Up Function

Anonymous — Mon, 16 Mar 2015 18:28:13 GMT

I can pull the qvd from either or qvw and build the relationship through the script. I just have never used the Apply Map in this instance. Can someone present an example?

Re: Look Up Function

Anonymous — Mon, 16 Mar 2015 18:33:06 GMT

So essential I am just flagging duplicates of Client ID numbers

Re: Look Up Function

Anonymous — Mon, 16 Mar 2015 18:48:22 GMT

Maybe something like :

FlagMap:

Mapping Load

HistoryClientID ,

'Yes' as DuplicateFlag

from History.qvd (qvd) ;

Then when you loading the other data, add an ApplyMap something like this :

ApplyMap ( 'FlagMap' , ClientID , 'No' ) as DuplicateFlag ,