topic Re: Need to change column into rows in QlikView

Need to change column into rows

qlikapple — Wed, 25 Nov 2020 16:16:04 GMT

Re: Need to change column into rows

qlikapple — Thu, 02 May 2019 13:59:42 GMT

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

Re: Need to change column into rows

qlikapple — Thu, 02 May 2019 14:10:50 GMT

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

Re: Need to change column into rows

qlikapple — Thu, 02 May 2019 14:16:26 GMT

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

Re: Need to change column into rows

qlikapple — Fri, 03 May 2019 08:20:34 GMT

can anyone give reply to the query above

Re: Need to change column into rows

qlikapple — Fri, 03 May 2019 09:12:26 GMT

anyon eplease reply

Re: Need to change column into rows

qlikapple — Fri, 03 May 2019 09:48:13 GMT

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