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

[Anonymous]

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

[Anonymous]

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

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

[chrismarlow]

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).

[maxgro]

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

[Anonymous]

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?

[Anonymous]

So essential I am just flagging duplicates of Client ID numbers

[Anonymous]

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 ,