Focal Point
About order by

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/327102492

November 16, 2009, 03:48 AM
Yangyang
About order by
time
2008-01
2007-01
2008-04
2009-01

TABLE FILE SQLOUT
PRINT *
COMPUTE NEW521/A4 = SUBSTR(15, time, 1, 4, 4, NEW521);
COMPUTE NEW522/A2 = SUBSTR(15, time, 6, 7, 2, NEW522);
COMPUTE timetest/I6MTYY=EDIT (NEW522||NEW521) ;
BY timetest
END
-RUN


the result :

JAN 07
JAN 08
JAN 09
APR 08


How can i get the result like below :

JAN 07
JAN 08
APR 08
JAN 09


Thanks,
Yang


WebFOCUS 7.6.8
WebFOCUS 7.6.10
BI
FLEX
.NET
November 16, 2009, 04:23 AM
<JG>
Time (I think you mean date) should be in a date format.
Then use BY TIME NOPRINT BY TIMETEST
November 16, 2009, 05:21 AM
Danny-SRL
Yang,

You can convert your year-month field to a FOCUS MTYY format:

  
-* File yangyang1.feX
FILEDEF YANG DISK YANG.FTM
-RUN
-WRITE YANG 2008-01
-WRITE YANG 2007-01
-WRITE YANG 2008-04
-WRITE YANG 2009-01
-RUN
EX -LINES 4 EDAPUT MASTER,YANG,C,MEM
 FILENAME=YANG    , SUFFIX=FIX
 SEGMENT=YANG, SEGTYPE=S0
 FIELDNAME=DT,  ALIAS=DT,  USAGE=A7, ACTUAL=A7, $
-RUN
DEFINE FILE YANG
ADT/A6YYM=EDIT(DT,'9999$99');
FDT/MTYY=ADT;
END
TABLE FILE YANG
BY FDT
END

Then sort is natural.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

November 16, 2009, 07:14 AM
Stefan
Why don't you try:

TABLE FILE SQLOUT
PRINT *
BY time
END
-RUN


WF 7.6.9
PMF 5.1.3
BID 7.6.9

Win XP
HTML, PDF, Excel, PowerPoint
November 16, 2009, 03:29 PM
Waz
The reason the output is in this order is you are using an integer for the month/year.

The MTYY is just for output foratting.

You are sorting

012008
012007
042008
012009

You have several options, JG's is the easiest.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

November 16, 2009, 09:42 PM
Yangyang
Thanks JG

BY TIMETEST
BY TIME NOPRINT

this is ok,
but i use it in crete graph,
in the graph , it's not work well...
so i want use BY TIMETEST one time .
,
Thanks Danny-SRL ,
Thanks Stefan ,
Thanks Waz ,


i will try again , thanks all , Smiler

Thanks,
Yang


WebFOCUS 7.6.8
WebFOCUS 7.6.10
BI
FLEX
.NET
November 19, 2009, 01:32 AM
Yangyang
Hi Danny-SRL , thanks your replied.

i used your code,

DEFINE FILE YANG
ADT/A6YYM=EDIT(DT,'9999$99');
FDT/MTYY=ADT;
END
TABLE FILE YANG
BY FDT
END


i can get the :
JAN, 2007
JAN, 2008
APR, 2008
JAN, 2009


so , how to remove the "," to get the [JAN 2007] with natural sort


Thanks,
Yang

This message has been edited. Last edited by: Yangyang,


WebFOCUS 7.6.8
WebFOCUS 7.6.10
BI
FLEX
.NET
November 19, 2009, 04:25 AM
GamP
Try this:
DEFINE FILE YANG
ADT/A6YYM=EDIT(DT,'9999$99');
ALFA/A17 = CHGDAT('YYM', 'MYYT', ADT, 'A17');
FDT/MTYY=ADT;
END
TABLE FILE YANG
PRINT ADT ALFA BY FDT
END



GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
November 23, 2009, 08:49 PM
Yangyang
Thanks GamP

actually , i want to use this in create Graph.

so i want to order by time(JAN 2007)

if it is "A17" , i think the sort is jumbled .

i used the Danny-SRL's code , it's ok except "," ...



Thanks,
Yang


WebFOCUS 7.6.8
WebFOCUS 7.6.10
BI
FLEX
.NET
November 24, 2009, 04:24 AM
Tony A
quote:
i used the Danny-SRL's code , it's ok except "," ...
The comma is part of that date format (MTYY) it is also grammatically correct even if it does extend the value by one extra character.

If you want the comma removed you will will have to control the sort order yourself using BY fieldname COLUMNS 'JAN 2007' AND ..... etc.

Check out the documentation so that you understand it fully before trying to use it.

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
November 24, 2009, 05:45 AM
Danny-SRL
quote:
The comma is part of that date format (MTYY) it is also grammatically correct


And then the possible aesthetical problem of an additional comma far outweigh the complexity of
BY sortfield ROWS val1 OR val2 ...

and of
ACROSS sortfield COLUMNS val1 AND val2 ...


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

November 24, 2009, 10:03 AM
Tony A
Ooooooooooooh, so true! Wink

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
November 24, 2009, 10:26 PM
Yangyang
Thanks all .

: )


Thanks,
Yang


WebFOCUS 7.6.8
WebFOCUS 7.6.10
BI
FLEX
.NET