Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all ,
I have the model which is like below
-------------------------------------------------
TEMP_Induced_Outage:
LOAD
//AutoNumberHash128(PROVIDER_INCIDENT_ID) AS %InducedOutageKey,
PROVIDER_INCIDENT_ID,
OUT_ID,
// SRV_ID,
// Date(Floor(Num(END_DATETIME)) ,'DD/MM/YYYY') AS Out_END_DATE,
// Date(Floor(Num(START_DATETIME)) ,'DD/MM/YYYY') AS Out_START_DATE,
Interval(END_DATETIME-START_DATETIME , 'mm') AS OUTAGE_DURATION,
NUM#(text(Interval(END_DATETIME-START_DATETIME , 'mm'))) AS OUTAGE_MINUTES,
//-------------------------------------------------------------------
//CONTROL_CODE,
//PROCESSED,
SCOPE_CODE,
applymap('Outage_Scope_PLADA',SCOPE_CODE,'#NA') AS SCOPE_NAME,
//SEVERITY,
//SIGNIFICANT,
//SOURCE_CODE,
STATUS_CODE
//ROOT_CAUSE_ID,
// ROOT_CAUSE_EXTENSION_DURATION,
// ROOT_CAUSE_EXTENSION_ID,
// IGNORE_IN_OUTAGE,
// SERVICE_RECIPIENT_LOCATION,
// BUSINESS_IMPACT,
// RESOLUTION
//---------------------------------------------------------------------
FROM [$(vPLADA_QVD_FOLDER_PATH)\OUTAGE.qvd] (qvd)WHERE STATUS_CODE <> 'D';
temp1_Induced_Outage:
load PROVIDER_INCIDENT_ID,
OUT_ID,
SCOPE_NAME,
OUTAGE_MINUTES,
if(MATCH(SCOPE_NAME,'DEGRADED'),'Y','N') as DEGRADESCOPE,
if(match(SCOPE_NAME,'DEGRADED'),sum(OUTAGE_MINUTES))as [Degraded Outage minutes]
Resident TEMP_Induced_Outage
group by PROVIDER_INCIDENT_ID,OUT_ID,SCOPE_NAME,OUTAGE_MINUTES;
concatenate
//join
//temp2_Induced_Outage:
load
PROVIDER_INCIDENT_ID,
OUT_ID as OUTAGEID,
SCOPE_NAME as SCOPENAME,
OUTAGE_MINUTES as OUTAGEMINUTES,
//if(match(SCOPE_NAME,'DEGRADED'),sum(OUTAGE_MINUTES))as [Degraded Outage minutes],
if(match(SCOPE_NAME,'ONLINE'),sum(OUTAGE_MINUTES))as [Full Outage Minutes],
if(MATCH(SCOPE_NAME,'ONLINE'),'Y','N') as ONLINESCOPE
Resident TEMP_Induced_Outage
group by PROVIDER_INCIDENT_ID,OUT_ID,SCOPE_NAME,OUTAGE_MINUTES;
Induced_Outage:
Load *,
AutoNumberHash128(PROVIDER_INCIDENT_ID) AS %InducedOutageKey
resident temp1_Induced_Outage;
drop table temp1_Induced_Outage;
drop fields PROVIDER_INCIDENT_ID,OUT_ID,SCOPE_NAME,OUTAGE_MINUTES;
drop table TEMP_Induced_Outage;
---------------------------------------------------
and in the front end i am taking straight table as
i need the 0/p as
In the straight table qlikview, i am getting like this in which rows are repeating
but i need like this
for CR2544392 we got only one line , i need the o /p as one line just like below. how to do
Change Order Number | Disruptive change (Full) | Full Outage Minutes | Disruptive change (Degraded) | Degraded Outage minutes | Disruptive Free change |
CR2544933 | N | 0 | N | 0 | Y |
CR2576841 | N | 0 | N | 0 | Y |
CR2544392 | Y | 355 | N | 12 | N |
CR2568882 | N | 0 | N | 0 | Y |
CR2607697 | N | 0 | N | 0 | Y |
can anyone give reply to the query above
Actually i have written the code as TEMP_Induced_Outage: LOAD PROVIDER_INCIDENT_ID, OUT_ID, Interval(END_DATETIME-START_DATETIME , 'mm') AS OUTAGE_DURATION, NUM#(text(Interval(END_DATETIME-START_DATETIME , 'mm'))) AS OUTAGE_MINUTES, SCOPE_CODE, applymap('Outage_Scope_PLADA',SCOPE_CODE,'#NA') AS SCOPE_NAME, STATUS_CODE FROM [$(vPLADA_QVD_FOLDER_PATH)\OUTAGE.qvd] (qvd)WHERE STATUS_CODE <> 'D'; temp1_Induced_Outage: load PROVIDER_INCIDENT_ID, OUT_ID, SCOPE_NAME, OUTAGE_MINUTES, if(MATCH(SCOPE_NAME,'DEGRADED'),'Y','N') as DEGRADESCOPE, if(match(SCOPE_NAME,'DEGRADED'),sum(OUTAGE_MINUTES))as [Degraded Outage minutes], if(match(SCOPE_NAME,'ONLINE'),sum(OUTAGE_MINUTES))as [Full Outage Minutes], if(MATCH(SCOPE_NAME,'ONLINE'),'Y','N') as ONLINESCOPE Resident TEMP_Induced_Outage group by PROVIDER_INCIDENT_ID,OUT_ID,SCOPE_NAME,OUTAGE_MINUTES; left keep(CHG_Induced_IN) //inner join(CHG_Induced_IN) Induced_Outage: Load *, AutoNumberHash128(PROVIDER_INCIDENT_ID) AS %InducedOutageKey resident temp1_Induced_Outage; drop table temp1_Induced_Outage; drop table TEMP_Induced_Outage; in this i no/P as