Rolling up Data in Qlikview

[#1]

Hello All,

I am currently facing some issue in creating a rolled-up view of the data I have. Given, I have the following data:

Element_Name     Remediation_Status     Severity_Level

E1                                 Step2                     Medium

E1                                 Step3                     High

E1                                 Step4                     Medium

E2                                 Step1                     Low

E2                                   -                              -

E2                                 Step2                     Medium

I have to create a rolled-up data such that for a given Element_Name, I have the lowest step for Remediation_Status and highest level of Severity_Level. The data should look something like this:

Element_Name     Remediation_Status     Severity_Level

E1                                 Step2                     Medium

E2                                 Step1                     Medium

Can this be done? Please Help!!

[MK_QSL · Accepted solution]

Test:

Load

  Element_Name,

  IF(NOT ISNULL(Remediation_Status) or LEN(TRIM(REPLACE(Remediation_Status,'-','')))<>0,Remediation_Status,NULL()) as Remediation_Status,

  IF(NOT ISNULL(Severity_Level) or LEN(TRIM(REPLACE(Severity_Level,'-','')))<>0,Severity_Level,NULL()) as Severity_Level

Inline

[

  Element_Name, Remediation_Status, Severity_Level

  E1,             Step2,       Medium

  E1,             Step3,       High

  E1,             Step4,       Medium

  E2,             Step1,       Low

  E2,             -  ,       -

  E2,             Step2,       Medium

];

NoConcatenate

Final:

Load

  Element_Name,

  FirstSortedValue(Remediation_Status,NUM(KeepChar(Remediation_Status,'0123456789'))) as Remediation_Status,

  FirstSortedValue(Severity_Level,NUM(KeepChar(Remediation_Status,'0123456789'))) as Severity_Level

Resident Test

Group By Element_Name;

Drop Table Test;

[MK_QSL]

Are you sure the E2 security level should come Medium?

[#4]

Yes, if it's null, then I have to ignore that record.

[MK_QSL]

Then change

FirstSortedValue(Severity_Level,-NUM(KeepChar(Remediation_Status,'0123456789'))) as Severity_Level

you will get your desired result...

[#6]

Thanks Manish,

But is there a way to do it without load statement? I do not want this as a static field. Basically, the Remediation_Status and the Severity_Level will keep changing based on the specific filters I will select.

[MK_QSL]

Create a Straight Table

Dimension

Element_Name

Expressions

FirstSortedValue(Remediation_Status,NUM(KeepChar(Remediation_Status,'0123456789')))

and

FirstSortedValue(Severity_Level,-NUM(KeepChar(Remediation_Status,'0123456789')))

[#8]

Thank you Manish. This works. But I have a follow-up question. I have to create a new field in the script based on the value of Remediation_Status. I have tried the following:

Element_Metric,

     Key,

     Remediation_Status,

     Severity_Level,

     No_of_Records,

     Flag_Below_Threshold

   

    FROM

C:\Users\nwalia\Desktop\Qlikview\Data\element_dq_trend.xls

(biff, embedded labels);





Temp:    

  Load *,

  If(Remediation_Status="Issue Intake",1) as Remediation_Flag,

  If(Remediation_Status="Triaging",2) as Remediation_Flag,

  If(Remediation_Status="Root Cause Analysis",3) as Remediation_Flag,

  If(Remediation_Status="Remediation Plan Development",4) as Remediation_Flag

  Resident Final;   

This gives me an error that "Issue Intake" field not found. What am I doing wrong?

[MK_QSL]

Load *,

  If(Remediation_Status="Issue Intake",1 ,

     If(Remediation_Status="Triaging",2,
          If(Remediation_Status="Root Cause Analysis",3,
               If(Remediation_Status="Remediation Plan Development",4)))) as Remediation_Flag

  Resident Final;  

[#10]

This gives me the same error- Field not found: <Issue Intake>