topic Re: changing column into row in QlikView

changing column into row

qlikapple — Sat, 16 Nov 2024 03:46:35 GMT

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

Re: changing column into row

sunny_talwar — Wed, 24 Apr 2019 12:22:40 GMT

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

Re: changing column into row

qlikapple — Wed, 24 Apr 2019 12:38:02 GMT

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

Re: changing column into row

sunny_talwar — Wed, 24 Apr 2019 12:59:23 GMT

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;

Re: changing column into row

qlikapple — Thu, 25 Apr 2019 11:16:39 GMT

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

sunny_talwar — Thu, 25 Apr 2019 11:21:35 GMT

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

Re: changing column into row

qlikapple — Thu, 25 Apr 2019 11:35:10 GMT

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

sunny_talwar — Thu, 25 Apr 2019 11:38:26 GMT

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?

Re: changing column into row

qlikapple — Thu, 25 Apr 2019 11:53:49 GMT

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

Re: changing column into row

qlikapple — Thu, 25 Apr 2019 11:56:44 GMT

if i add OUTAge ID , iam attaching the screen shots

Re: changing column into row

sunny_talwar — Thu, 25 Apr 2019 11:59:29 GMT

Yes that is because you have OUT_ID differences... may be you can combine the OUT_ID separated by comma?

Concat(DISTINCT OUT_ID, ', ') as OUT_ID

Re: changing column into row

qlikapple — Thu, 25 Apr 2019 12:02:28 GMT

I need the red rounded ones in one column

Re: changing column into row

sunny_talwar — Thu, 25 Apr 2019 12:04:34 GMT

Give shot to have I mentioned above (Concat)... if that doesn't work... please provide a sample where we can see what you have and also provide details as to what you want to do with OUT_ID... meaning... if concatenating them okay?

Re: changing column into row

qlikapple — Thu, 25 Apr 2019 12:18:36 GMT

Hi Sunny,

i have done like this

LOAD * INLINE [
CRNO, OUTSCOPE,OUTID, OUTAGEMINUTES, IN NO
1234, ONLINE, ABC,32, 11234
1234, DEGRADE, !@C, , 11234
2341, ONLINE,asv, 21, ABC
2341, DEGRADE,qwe, 32, ABC
2341, DEGRADE,erd, 43, ABC
123456, DEGRADE,erd, 21, 2312
123456, DEGRADE,des, 32, 2312
123456, DEGRADE,dsc, 21, 2312
123456, ONLINE,ded, 21, 2312
];

FinalTable:
LOAD
Concat(DISTINCT OUTID, ', ') as OUT_ID,
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
Concat(DISTINCT OUTID, ', ') as OUT_ID,
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;

I am getting like this

Re: changing column into row

sunny_talwar — Thu, 25 Apr 2019 12:27:15 GMT

How about something like this

Table:
LOAD * INLINE [
    CRNO, OUTSCOPE, OUTID, OUTAGEMINUTES, IN NO
    1234, ONLINE, ABC, 32, 11234
    1234, DEGRADE, !@C, , 11234
    2341, ONLINE, asv, 21, ABC
    2341, DEGRADE, qwe, 32, ABC
    2341, DEGRADE, erd, 43, ABC
    123456, DEGRADE, erd, 21, 2312
    123456, DEGRADE, des, 32, 2312
    123456, DEGRADE, dsc, 21, 2312
    123456, ONLINE, ded, 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 Fields OUTSCOPE, OUTAGEMINUTES, [IN NO];

You will get your table like this

and yet have OUTID in your app in a separate table