Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
sahajaqlik
Contributor

changing column into row

Hi please find the dummy data

CRNOOUTSCOPEOUTAGEMINUTESIN NO
    
1234ONLINE3211234
1234DEGRADE 11234
2341ONLINE21ABC
2341DEGRADE32ABC
2341ONLINE43ABC
123456DEGRADE212312
123456DEGRADE322312
123456DEGRADE212312
123456ONLNE212312

 

 

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

 

CRNOOUTSCOPE(ONLINE Y)ONLINE MINUTESOUTSCOPE(DEGARDE Y)DEGRADE MINUTESIN NO
1234Y32N011234
2341Y21Y75ABC
123456Y21Y742312
Labels (1)
14 Replies

Re: changing column into row

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

sahajaqlik
Contributor

Re: changing column into row

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

Re: changing column into row

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;
sahajaqlik
Contributor

Re: changing column into row

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

 

Re: changing column into row

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

sahajaqlik
Contributor

Re: changing column into row

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

Re: changing column into row

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?

sahajaqlik
Contributor

Re: changing column into row

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 NumberDisruptive change (Full)Full Outage MinutesDisruptive change (Degraded)Degraded Outage minutes
CR2618555Y160N361

 
how to do that



sahajaqlik
Contributor

Re: changing column into row

if i add OUTAge ID , iam attaching the screen shots