How to create expression, where the joined link is shared, however the value only store at one field class but others are null as per excel below.

[asrif1983]

Im not sure how to phrase the question, if anyone may have a look at the excel. i need the pivot to result like that. issue is the other class doesnt have the occupation value, but it could use the fire class occupation using the policy number. something to use with possible function?

[Lisa_P]

This is best done in the load script as per my example below ...

[RiskTable]:

Load [Policy Number & RiskNo],

PolicyNumber,

    [Risk No],

    [Class],

    If(Len(OccupationOrig)<2, Peek(Occupation), OccupationOrig) as Occupation;

LOAD * INLINE [

    Policy Number & RiskNo,PolicyNumber,Risk No,OccupationOrig,Class

    x11,x1,1,Beverage,Fire

    x12,x1,2,,Liability

    x13,x1,3,,Engineering

    x14,x1,4,,Group Pa

    x21,x2,1,F N B ,Fire

    x22,x2,2,,Liability

    x23,x2,3,,Engineering

    x24,x2,4,,Group Pa

](delimiter is ',');

[TransactionTable]:

LOAD * INLINE [

    GWP,PolicyNumber,Policy Number & RiskNo

    100,x1,x11

    200,x1,x12

    300,x2,x21

    400,x2,x22

](delimiter is ',');