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]

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

[qlikapple]

I need the red rounded ones in one column

[sunny_talwar]

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?

[qlikapple]

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

[sunny_talwar]

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