Sort Data by Week Number

Posted onApril 16, 2014

Sort Data by Week Number

There are times when doing reports when you need to sort your values into weeks.
Most people would end up writing a program to do this, but that is not necessary.

So assuming attribute 14 contains a date, then

F Correlative
F;14;C7;/
A Correlative
A;(14 / “7”)

A full dictionary item would like the like the following
WEEK.NBR
001 S
002 0
003 Week Number
008 F;14;C1;+;C7;/
009 R
010 6

WEEK.NBR is now the week number since 1968, with Sunday as the start of the week. You can then
use this in a BREAK-ON.

SORT FILE BY DATE BREAK-ON WEEK.NBR

If you want to have Monday as the beginning of the week, then do the follow:

WEEK.NBR.M-S
001 S
002 0
003 Week Number
008 F;14;C1;-;C7;/
009 R
010 6

If you want to sort based on the week number within the year, use:

WEEK.NBR.IN-YR
001 S
002 0
003 Week Number
008 A;(14 / “7”)-((“01/01/”:(14(DY)))(DI) / ”7”)
009 R
010 6

Credit to Tom Phillips.

DICT attribute ‘9’ isn’t only used for column justification!

Posted onApril 15, 2014

Watch out for records that mix alpha and numeric characters in the same field. If a field takes a value which is expected and used as numeric but for whatever reason it’s append and stored with alpha chars.

For example: In the file SALES the value of attribute LOT-NO is always numeric but in the file RETURNS the value of attribute LOT-NO has alpha characters are appended.

JQL searches records based on the justification specified in DICT attribute 9.  Specifying ‘R’ searches right to left. Specifying ‘L’ searches left to right. Additionally ‘R’ will align right and ‘L’ will align left when displayed and/or printed.

Example: in the file named RETURNS

If the value of LOT-NO is 123456.1(1234) it will not be located when DICT LOT-NO specifies attribute 9 as R. JQL will look at LOT-NO starting on the right which begins with ‘)’ and not on the left which begins with ‘1’

SELECT RETURNS WITH LOT-NO = “123456]” – You might think this record, which begins with 123456, would be returned using ‘]’. However because attribute 9 specifies R it will not.

DICT RETURNS

LOT-NO

0001 A
0002 1
0003 Lot Number
0004
0005
0006
0007
0008
0009 R
0010 15

If the value of LOT-NO is 123456.1(1234) it will be located if DICT LOT-NO specifies attribute 9 as L. JQL will look at LOT-NO starting on the left which begins with ‘1’ and not on the right which begins with ‘)’

SELECT RETURNS WITH LOT-NO = “123456]” – As you would expect, because this record begins with 123456 it will be returned using the ‘]’ because attribute 9 specifies L.

DICT RETURNS

LOT-NO

0001 A
0002 1
0003 Lot Number
0004
0005
0006
0007
0008
0009 L
0010 15

Surprise!