Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikapple
Creator
Creator

Need to change column into rows

 
6 Replies
qlikapple
Creator
Creator
Author

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

 

 

 

qlikapple
Creator
Creator
Author

In the straight table qlikview, i am getting like this in which rows are repeating

 

qlikapple
Creator
Creator
Author

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 NumberDisruptive change (Full)Full Outage MinutesDisruptive change (Degraded)Degraded Outage minutesDisruptive Free change
CR2544933N0N0Y
CR2576841N0N0Y
CR2544392Y355N12N
CR2568882N0N0Y
CR2607697N0N0Y
qlikapple
Creator
Creator
Author

can anyone give reply to the query above

qlikapple
Creator
Creator
Author

anyon eplease reply
qlikapple
Creator
Creator
Author

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