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

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

durgabhavani — Thu, 22 Mar 2018 13:22:38 GMT

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

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

sunny_talwar — Thu, 22 Mar 2018 13:29:58 GMT

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

];

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

josefmyers267 — Thu, 22 Mar 2018 13:43:10 GMT

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;

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

durgabhavani — Thu, 22 Mar 2018 14:07:44 GMT

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

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

sunny_talwar — Thu, 22 Mar 2018 14:11:08 GMT

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

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

durgabhavani — Thu, 22 Mar 2018 14:18:09 GMT

Thank you Sunny.

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

durgabhavani — Thu, 22 Mar 2018 15:15:20 GMT

Sunny, One query here.

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

Please advice.

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

sunny_talwar — Thu, 22 Mar 2018 15:16:38 GMT

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

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

durgabhavani — Thu, 22 Mar 2018 15:23:09 GMT

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

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

sunny_talwar — Thu, 22 Mar 2018 15:29:35 GMT

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

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

durgabhavani — Thu, 22 Mar 2018 15:42:38 GMT

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.

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

sunny_talwar — Thu, 22 Mar 2018 15:45:02 GMT

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

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

durgabhavani — Thu, 22 Mar 2018 15:52:01 GMT

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

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

durgabhavani — Thu, 22 Mar 2018 15:53:19 GMT

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

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

sunny_talwar — Thu, 22 Mar 2018 15:58:35 GMT

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

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

durgabhavani — Thu, 22 Mar 2018 16:01:48 GMT

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

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

sunny_talwar — Thu, 22 Mar 2018 16:10:41 GMT

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

];

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

durgabhavani — Thu, 22 Mar 2018 16:23:00 GMT

Thank you very much sunny. I will try this.

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

sunny_talwar — Thu, 22 Mar 2018 16:24:07 GMT

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

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

durgabhavani — Fri, 23 Mar 2018 13:08:36 GMT

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