changing column into row

[qlikapple]

Hi please find the dummy data

CRNO	OUTSCOPE	OUTAGEMINUTES	IN NO
1234	ONLINE	32	11234
1234	DEGRADE		11234
2341	ONLINE	21	ABC
2341	DEGRADE	32	ABC
2341	ONLINE	43	ABC
123456	DEGRADE	21	2312
123456	DEGRADE	32	2312
123456	DEGRADE	21	2312
123456	ONLNE	21	2312

The requirement is like this , I want to show IN NO as single(unique) so that it should not repeat again ,
Now I want the o/p as

Now i want the O/P as

Anyone please help

CRNO	OUTSCOPE(ONLINE Y)	ONLINE MINUTES	OUTSCOPE(DEGARDE Y)	DEGRADE MINUTES	IN NO
1234	Y	32	N	0	11234
2341	Y	21	Y	75	ABC
123456	Y	21	Y	74	2312

[sunny_talwar]

Are you looking to do this in the script or on a front end chart?

[qlikapple]

I need in backend, can you please check ,i want incident no as unique here

[sunny_talwar]

Try this

Table:
LOAD * INLINE [
    CRNO, OUTSCOPE, OUTAGEMINUTES, IN NO
    1234, ONLINE, 32, 11234
    1234, DEGRADE,  , 11234
    2341, ONLINE, 21, ABC
    2341, DEGRADE, 32, ABC
    2341, DEGRADE, 43, ABC
    123456, DEGRADE, 21, 2312
    123456, DEGRADE, 32, 2312
    123456, DEGRADE, 21, 2312
    123456, ONLINE, 21, 2312
];

FinalTable:
LOAD CRNO,
	 [IN NO],
	 If(Sum(OUTAGEMINUTES) > 0, 'Y', 'N') as [OUTSCOPE(ONLINE Y)],
	 Sum(OUTAGEMINUTES) as [ONLINE MINUTES]
Resident Table
Where OUTSCOPE = 'ONLINE'
Group By CRNO, [IN NO];

Left Join (FinalTable)
LOAD CRNO,
	 [IN NO],
	 If(Sum(OUTAGEMINUTES) > 0, 'Y', 'N') as [OUTSCOPE(DEGARDE Y)],
	 Sum(OUTAGEMINUTES) as [DEGARDE MINUTES]
Resident Table
Where OUTSCOPE = 'DEGRADE'
Group By CRNO, [IN NO];

DROP Table Table;

[qlikapple]

Hi Sunny, I have OUTID which is diffrenet also in the table , how to get that suppose example: Table:

LOAD * INLINE [ CRNO, OUTSCOPE,OUTID, OUTAGEMINUTES, IN NO

1234, ONLINE, ABC,32, 11234

1234, DEGRADE, BCD , 11234

2341, ONLINE,ASC, 21, ABC

2341, DEGRADE, DFC,32, ABC

2341, DEGRADE,MJK, 43, ABC

123456, DEGRADE, ERD,21, 2312

123456, DEGRADE, FCD,32, 2312

123456, DEGRADE, GTY,21, 2312

123456, ONLINE, GDTY,21, 2312 ];

then how can we do using Group by ( actually you are using elow left join) ,i need the OUT iD also in the datamodel as the OUTGAE MINUTES are depende on OUTID and OUTSCOPE.

Can you please help once

I need the O/P as attached

[sunny_talwar]

You didn't mention how the OUTID needs to look in your final output? The image is without OUTID in it...

[qlikapple]

In the Final O/P i dont want OutageID.

But in the model as you have given you have taken left join(in which only first table data ony will apeear)

i want the image as exactly you have given , but the problem is OUT ID are different,

can you please check and let me know  how to do that

If i am taking GRoup By  based on CRNO ,IN NO ,OUTID i am getting data only for the first one, how to do that

[sunny_talwar]

I understand that, but how would you want to keep the OUTID as in the table? Can you show me this in an Excel file?

[qlikapple]

hi sunny,

i have written the script like this

TEMP_Induced_Outage:

LOAD
AutoNumberHash128(PROVIDER_INCIDENT_ID) AS %InducedOutageKey,
PROVIDER_INCIDENT_ID,
OUT_ID,
applyMap('OUTAGE_SCOPE_NAME',SCOPE_CODE,'#NA') AS [SCOPENAME],
//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

FROM [$(vPLADA_QVD_FOLDER_PATH)\OUTAGE.qvd] (qvd)
;





ONLINE_OUTAGE:

LOAD
PROVIDER_INCIDENT_ID,
OUT_ID,
SCOPENAME,
if(MATCH(SCOPENAME,'ONLINE'),'Y','N') as [Disruptive change (Full)],
// If(Sum(OUTAGE_MINUTES) > 0, 'Y', 'N') as [Disruptive change (Full)],
Sum(OUTAGE_MINUTES) as [ONLINE MINUTES]

resident TEMP_Induced_Outage Where match(SCOPENAME,'ONLINE')

Group By PROVIDER_INCIDENT_ID,OUT_ID,SCOPENAME;


Join (ONLINE_OUTAGE)
//left Join (ONLINE_OUTAGE)

LOAD
PROVIDER_INCIDENT_ID,
OUT_ID ,
SCOPENAME,
if(MATCH(SCOPENAME,'DEGRADED'),'Y','N') as [Disruptive change (Degraded)],
//If(Sum(OUTAGE_MINUTES) > 0, 'Y', 'N') as [Disruptive change (Degraded)],
Sum(OUTAGE_MINUTES) as [DEGRADED MINUTES]

resident TEMP_Induced_Outage Where match(SCOPENAME,'DEGRADED')

Group By PROVIDER_INCIDENT_ID,OUT_ID,SCOPENAME;
//PROVIDER_INCIDENT_ID,OUT_ID,SCOPE_NAME


DROP Table TEMP_Induced_Outage;

left keep(CHG_Induced_IN)

Induced_Outage:

load *,
AutoNumberHash128(PROVIDER_INCIDENT_ID) AS %InducedOutageKey
resident ONLINE_OUTAGE;
drop table ONLINE_OUTAGE;

  I am getting the O/p as  attached

But i want to show as

Change Order Number	Disruptive change (Full)	Full Outage Minutes	Disruptive change (Degraded)	Degraded Outage minutes
CR2618555	Y	160	N	361

 
how to do that

[qlikapple]

if i add OUTAge ID , iam attaching the screen shots