Help me to derive new field based on my two fields?

[durgabhavani]

Hi All,

Can you please help me to compare two fields and derive new field (New Severity). Please find my sample data and expected output column.

Initial Severity	Latest Severity	New Severity (Expected Output)
High	Low	N/A
Low	Med	Med
Med	High	High
High	Low	N/A
Med	High	High
Low	High	High
Med	Low	N/A
High	Med	N/A
Low	Med	Med

[durgabhavani]

Got this point. just now comment the first part of the code in sample and ran the application.

Able to get below output. How are values 1,2,3 assigned to rank fields. (Is that match funcitionality?) Confused. Please explain.

[sunny_talwar]

Yup, Match is doing that....

Assigns the number based on the value

Match([Initial Severity], 'Low', 'Med', 'High')

Low is 1st... so gets 1

Med is 2nd... so gets 2

High is 3rd... so gets 3

[durgabhavani]

Not working as expected. see below. highlighted records are wrong. Expecting N/A instead of Med, Med instead of N/A on highlighted simulatenously.

[durgabhavani]

Ok. Can we assign directly numbers to high, med, low instead of match?

[sunny_talwar]

ApplyMap is another option... but what is wrong with Match?

[durgabhavani]

due to my existing code. May be not able to use Match function. Can you tell me how can we use applymap here.

[sunny_talwar]

Here you go

MappingLoad:

Mapping

LOAD * INLINE [

    F1, F2

    High, 3

    Med, 2

    Low, 1

];

Table:

LOAD RowNo() as RowNum,

[Initial Severity],

[Latest Severity],

If([Latest Severity Rank] > [Initial Severity Rank], [Latest Severity], 'N/A') as [New Severity];

LOAD *,

ApplyMap('MappingLoad', [Initial Severity], Null()) as [Initial Severity Rank],

ApplyMap('MappingLoad', [Latest Severity], Null()) as [Latest Severity Rank];

LOAD * INLINE [

    Initial Severity, Latest Severity

    High, Low

    Low, Med

    Med, High

    High, Low

    Med, High

    Low, High

    Med, Low

    High, Med

    Low, Med

];

[durgabhavani]

Thank you very much sunny. I will try this.

[sunny_talwar]

Not sure if this will not cause the same issue as the other solution.... it is essentially doing the same thing

[durgabhavani]

Thanks sunny. Applymap can be used perfectly with existing code and able to get expected output.