Field not found Error

[#1]

Hi I am loading data in qlikview using the script below:

Static_Response:

LOAD * INLINE [

    Source_ID, Severity, Defined_Response_SLA

    IM, Critical, 5

    IM, Major, 5

    IM, Medium, 15

    IM, Minor, 30

    IM, Outage, 5

    SR, Critical, 15

    SR, Major, 15

    SR, Medium, 30

    SR, Minor, 60

];

Static_Resolution:

LOAD * INLINE [

    Source_ID, Severity, Defined_Resolution_SLA

    IM, Critical, 30

    IM, Major, 60

    IM, Medium, 240

    IM, Minor, 1440

    IM, Outage , 60

    SR, Critical, 120

    SR, Major, 480

    SR, Medium, 1440

    SR, Minor, 10080

];

All_Issues:

LOAD [Ticket No],

     TCM# as Response_Time,

     Source,

     if(Source='Incident','IM','SR') as Source_ID,

     [Date of Incident],

     Month([Date of Incident]) as Incident_Month,

     Category,

     Application,

     Severity,

     [Root Cause Description],

     [Issue Duration] as Resolution_Time,

     if(TCM# < Defined_Response_SLA,'YES','NO') as Response_SLA_Met_Code,

     if([Issue Duration] < Defined_Resolution_SLA,'YES','NO') as Resolution_SLA_Met_Code

FROM

[Copy of SLO_Sept (3).xlsx]

(ooxml, embedded labels, table is [All issues], filters(

Remove(Col, Pos(Top, 6)),

Remove(Col, Pos(Top, 5)),

Remove(Col, Pos(Top, 20)),

Remove(Col, Pos(Top, 19)),

Remove(Col, Pos(Top, 18)),

Remove(Col, Pos(Top, 17)),

Remove(Col, Pos(Top, 16)),

Remove(Col, Pos(Top, 14)),

Remove(Col, Pos(Top, 13)),

Remove(Col, Pos(Top, 11)),

Remove(Col, Pos(Top, 10)),

Remove(Col, Pos(Top, 9)),

Remove(Col, Pos(Top, 8))

));

When I run the above script, I get fields Defined_Response_SLA and Defined_Resolution_SLA not found error.

Could someone please help me in resolving this error?

Thanks

Karan

[swuehl]

I assume these two fields are not part of the excel file. You can't reference fields located in other tables like this, you need to Lookup(), map or join the field values.

[#3]

Hi,

It was my first guess as well.

Thanks for the advice. I will try out the options you suggested.

[#4]

I changed my script to:

Static_Resolution_and_Response:

LOAD * INLINE [

    Source_ID, Severity, Defined_Response_SLA, Defined_Resolution_SLA

    IM, Critical, 5, 30

    IM, Major, 5, 60

    IM, Medium, 15, 240

    IM, Minor, 30, 1440

    IM, Outage, 5, 60

    SR, Critical, 15, 120

    SR, Major, 15, 480

    SR, Medium, 30, 1440

    SR, Minor, 60, 10080

];

Inner join (Static_Resolution_and_Response)

All_Issues:

LOAD [Ticket No],

     TCM# as Response_Time,

     Source,

     if(Source='Incident','IM','SR') as Source_ID1,

     [Date of Incident],

     Month([Date of Incident]) as Incident_Month,

     Category,

     Application,

     Severity1,

     [Root Cause Description],

     [Issue Duration] as Resolution_Time,

     if(TCM# < Defined_Response_SLA,'YES','NO') as Response_SLA_Met_Code,

     if([Issue Duration] < Defined_Resolution_SLA,'YES','NO') as Resolution_SLA_Met_Code

FROM

[Copy of SLO_Sept (3).xlsx]

(ooxml, embedded labels, table is [All issues], filters(

Remove(Col, Pos(Top, 6)),

Remove(Col, Pos(Top, 5)),

Remove(Col, Pos(Top, 20)),

Remove(Col, Pos(Top, 19)),

Remove(Col, Pos(Top, 18)),

Remove(Col, Pos(Top, 17)),

Remove(Col, Pos(Top, 16)),

Remove(Col, Pos(Top, 14)),

Remove(Col, Pos(Top, 13)),

Remove(Col, Pos(Top, 11)),

Remove(Col, Pos(Top, 10)),

Remove(Col, Pos(Top, 9)),

Remove(Col, Pos(Top, 8))

))

where Source_ID = Source_ID1

and   Severity = Severity1;

But now I am getting Source_ID field not found error. This time I am using a join.

What could be the problem here?

[nirav_bhimani]

Hi,

First you need to join the tables then apply your Where condition on the Below Main table like as follows.

I think you are joining first 2 Inline with the All_Issues tables, so you will get single table which is having all 3 tables data. take Resident of that table like below then it will work.

No Concatenate

Load * Resident Static_Resolution_and_Response

Where

Source_ID = Source_ID1

and   Severity = Severity1;

Drop table All_Issues tables;

Make sure the above table should have all the joining table & field which are used in the Where condition.

Thanks & Regards,

Nirav Bhimani