topic Re: Field not found Error in QlikView

Field not found Error

Wed, 01 Jan 2014 13:01:26 GMT

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

Re: Field not found Error

swuehl — Wed, 01 Jan 2014 13:16:16 GMT

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.

Re: Field not found Error

Wed, 01 Jan 2014 13:20:15 GMT

Hi,

It was my first guess as well.

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

Re: Field not found Error

Wed, 01 Jan 2014 13:50:55 GMT

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?

Re: Field not found Error

nirav_bhimani — Wed, 01 Jan 2014 14:28:30 GMT

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