Qlikview Join (Unconnected Data)

[racer25]

Hi,

I have 2 table that support Risk, Insurer and Commission Rate data.

Table (Risk), contains Risk Code, Insurer Code and Prem Rate. The challenge is that the table supports a * (asterisk) for all others. In essence we populate agreed risk/insurer rates and asterisk leaves us a default.

The second table has a list of all Insurers.

I am looking to replace all the wildcard entries in Qlikview and populate with missing Insurer's.

I have attached an extract of the 2 tables and a desired output.

I was looking at a combination of Exists and Not Exists but not making a whole heap of progress (none really(.

Any thoughts on how I might acheive this ?

Thanks in advance,

Rob

[marcus_sommer · Accepted solution]

I think I would try the following approach:

m: mapping load BRR_INSURERCODE, BRR_PREMIUMRATE from [RISK TABLE];

load *, applymap('m', INS_INSURERCODE, applymap('m', '*', '#NV')) as BRR_PREMIUMRATE
from INSURER;

In your real case it might not be enough because BRR_RISKCODE isn't included yet but if it's really not in INSURER available it could be added to the mapping-source with a cartesian join or maybe other methods.

- Marcus

[Brett_Bleess]

Did Marcus' suggestion work, or are you still trying to sort things out?  If the recommendation worked, please click the Accept as Solution button on it, if you cannot, just leave a post that it did do the trick, and we can take care of marking it for you.

Regards,
Brett