Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
durgabhavani
Creator III
Creator III

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

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 SeverityLatest SeverityNew Severity (Expected Output)
HighLowN/A
LowMedMed
MedHighHigh
HighLowN/A
MedHighHigh
LowHighHigh
MedLowN/A
HighMedN/A
LowMed

Med

1 Solution

Accepted Solutions
sunny_talwar

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

];

Capture.PNG

View solution in original post

21 Replies
sunny_talwar

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

];

Capture.PNG

josefmyers267
Contributor
Contributor

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

Thank you Sunny.

durgabhavani
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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

Capture.PNG