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

[sunny_talwar · Accepted solution]

May be this

Table:

LOAD RowNo() as RowNum,

[Initial Severity],

[Latest Severity],

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

LOAD *,

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

Match([Latest Severity], 'Low', 'Med', 'High') 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

];

[josefmyers267]

Try a formula like this in the script:

Severity_Table:

Load

          Initial_Severity,

          Latest_Severity,

          IF(Latest_Severity='High', 'High',

               IF(Latest_Severity='Med' and Initial_Severity<>'Low','Med',

                    IF(Latest_Severity='Low' and Initial_Severity='Low','Low',

                         'N/A'))) as New_Severity

From YourData.QVD;

[durgabhavani]

Thanks for reply Sunny. It is working. Can you please explain in some words, how it is working...

[sunny_talwar]

Assigning numeric values to High (3), Med(2), Low(1) and then doing a comparison like this

If(Latest  > Initial... then use Latest otherwise use N/A

[durgabhavani]

Thank you Sunny.

[durgabhavani]

Sunny, One query here.

Do we need to use RowNo() compulsory. Is there any other optimized way.

Please advice.

[sunny_talwar]

RowNo() is not needed ... it was just to show all the rows on the front end of the application... but you can remove that

[durgabhavani]

I need all rows what ever i provided in sample. so we need to use RowNo() right?

[sunny_talwar]

All the rows will be there... just won't show up on the table box without a unique identifier in the table box. For example, the two rows circled are the same exact... they will be in your dashboard, but in a table box with these three fields... they will only show up once. If you do a count with these three fields as dimension... you will see that the count is 2

Does that make sense