I have the following columns


media_id dow_id on off
-------- ------ --- -----
780748 1 0 100
780748 1 500 2400
780748 2 0 100
780748 2 500 2400
780748 3 0 100
780748 3 500 2400
780748 4 0 100
780748 4 500 2400
780748 5 0 100
780748 5 500 2400
780748 6 0 100
780748 6 500 2400
780748 7 0 100
780748 7 500 2400

I want the transpose of the column to rows in the following format


1 2 3 4 5 6 7
----------------------------------------------------------------------------------------------------------------------------
media_id on off on off on off on off on off on off on off
-------- ------------ ------------ ------------ ------------ ------------ ------------ ------------

780748 0 100 0 100 0 100 0 100 0 100 0 100 0 100
500 2400 500 2400 500 2400 500 2400 500 2400 500 2400 500 2400

how to achieve this in webFOCUS?

Hi Kummy

try to put the media_id AS ACROSS and the rest of field as Detailed [PRINT]


hope that will help

Try:

  
DEFINE FILE Fn
FLOW/I1 = IF DOW_ID EQ LAST DOW_ID THEN 1 ELSE 0;
END
TABLE FILE Fn
SUM ON OFF
ACROSS DOW_ID AS ''
BY MEDIA_ID
BY FLOW NOPRINT
END

Make sure the data is coming in in DOW_ID order.

thank you all i used oracle analytical functions to fix the issue.

Kummy,

If you want to do this with WebFOCUS, the following code should do it for you:

TABLE FILE YOURDATA
SUM ON OFF
BY MEDIA_ID
ACROSS DOW_ID
END

I'm not sure why Alan B's example included a DEFINE field.

Mickey

To give the layout asked for! Your example won't give the 0 100 row.

Thanks for the clarification Alan. It is hard to see what was requested when the columns do not line up in the post.

I have a similar request, except I'm trying to get each value for a specific id on one row.

e.g.

ID VALUE
-- -----
00 0002
00 0005
00 8213
00 1232
01 2222
01 3333
01 2123

And from this get:

ID NEWFIELD
-- -------------------
00 0002 0005 8213 1232
01 2222 3333 2123

so the newfield can vary in length as I do not know how many VALUE exist per ID.

I had defined:

NEWFIELD = IF ID EQ LAST ID THEN (LAST VALUE | VALUE) ELSE VALUE;

but this doesn't continue to add the values together since it doesn't know that the previous field is now different. This would only save the current Value and the previous Value, but not remember any of the other values.

Anybody tackle this before?

Try:

NEWFIELD/A300V = IF ID EQ LAST ID THEN LAST NEWFIELD | ' ' | VALUE ELSE VALUE;

The V option allows the field to be concatenated to another field, as long as you are on a latter release.

Thanks for the response,

I figured it out about 5 minutes after posting the question... is it just me or you have to put yourself out there before coming out with a solution yourself.

I ended up with:
NEWFIELD/A200 = IF ID NE LAST ID THEN VALUE ELSE LJUST(200, LAST NEWFIELD, 'A190') || (' ' | VALUE);

The issue I had was with the length of field (which both solutions solve) and the other was with the weak concatenation. Once I put the strong concatenation it worked like a charm!

Of course, Leo, you are right, a strong concat will be needed.

Sometimes you just need a head-on-a-stick to get a solution.

Try the V format for alpha fields sometime, it is a v. nice option.