Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Hi,
It was my first guess as well.
Thanks for the advice. I will try out the options you suggested.
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?
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