booking-date[1],4 = Century
booking-date[5],2 = Month
booking-date[7],2 = Day
Our response:>in myfile >def year,1,4,display >def month,1,2,display >def day,1,2,display >ext year=a / 10000 >ext month=(a / 100) mod 100 >ext day=a mod 100 >list >xeq >IN MYFILE.NEIL.GREEN (0) >OUT $NULL (0) YEAR = 2005 MONTH = 2 DAY = 7You can also use the $edit function to isolate portions of the date and make the date more readable.
>in myfile >def dispdata,1,10,byte >ext dispdata=$edit(a,"9999 99 99") >list >xeq >IN MYFILE.NEIL.GREEN (0) >OUT $NULL (0) DISPDATA = 2005 02 07
Recently a customer asked how they could fix some of the Zip codes which were in the form of:
123450000
and are stored in a numeric J2 field.
The customer wanted to normalize those zip codes that had the four trailing zeroes to be 12345 instead of 123450000.
The first step we wanted to do was to determine all of the codes that needed to be updated and what the old zip would be and what the new zip would be.
>base membrs >get member-file >def new-zip,1,4,double >if zip > 99999 and (zip mod 10000) = 0 >ext account >ext zip >ext new-zip = zip / 10000 >list >xeq
So what is the above doing?
Well the if command looks at all records that are greater than 99999 and ends in the four zeroes, which is what the (zip mod 10000) = 0 is doing. This should isolate just those records that the customer wanted to fix!
Once we determined that we had the correct records selected we easily updated them with:
>base membrs >get member-file >if zip > 99999 and (zip mod 10000) = 0 >update >ext new-zip = zip / 10000 >list >xeq
While on training recently I was asked by a customer to find the difference between a due-date for a payment and the current date. However, they only wanted to list the dates that were overdue as the number of days overdue, and if the payment was not overdue then they should show a zero as the days difference.
Now the best way to calculate a difference between two days is to convert the date using the $days function and subtract the two dates.
The $days function converts any date to the number of days since 4713 BC, and is known as Julian Day number. Therefore you can perform or figure out the difference between two dates. The task below has a couple of tricks, the first is to use Suprtool to build a command file to get the current date into a variable in Julian Day format!
The second part of the script figures out the difference between the two dates, keep in mind that the customer wants to know which dates are "overdue" and only want to see those that are overdue, so we re-order the subtraction such that overdue payments will be a positive number and those payments that are not yet due, will be a negative number.
rm tdays tdays.sd
rm setdate
export EQ_DBSERVER=":8202"
suprtool << EOD_Suprtool
{ Set $mydate variable to todays date in julian day format }
base membrs,5,lookup
get member-file
num 1
def tdays,1,4,double
item tdays,date,julian
ext tdays=\$today
out tdays,link
xeq
in tdays
def tdayascii,1,7,byte
ext 'export MYJULDAY="'
ext tdayascii=\$edit(tdays,"zzzzzzz")
ext '"'
out setdate
exit
EOD_Suprtool
chmod +x setdate
. ./setdate
rm setdate
suprtool << EOD_Suprtool
set varsub on
in dates
def diff,1,4,double
item a,date,ccyymmdd
ext a
ext diff=(\$MYJULDAY - \$DAYS(A))
list
xeq
exit
EOD_Suprtool
Given some dates here is what you would see:
>IN dates (0) >OUT $NULL (0) A = 20131213 DIFF = 47 >IN dates (1) >OUT $NULL (1) A = 20131231 DIFF = 29 >IN dates (2) >OUT $NULL (2) A = 20140201 DIFF = -3 >IN dates (3) >OUT $NULL (3) A = 20140130 DIFF = -1 >IN dates (4) >OUT $NULL (4) A = 20140115 DIFF = 14 IN=5, OUT=5. CPU-Sec=1. Wall-Sec=1. >exitNow since we only want to see positive numbers, if we change the target type to be logical, we will only get the difference in days with a positive number and the negatives will become zero since the definition of a logical number is that it cannot be negative.
Neo%dev/source/suprtool: ./datediff
rm: setdate non-existent
SUPRTOOL/UXia/Copyright Robelle Solutions Technology Inc. 1981-2014.
(Version 5.6 Internal) WED, JAN 29, 2014, 11:55 AM Type H for help.
Build 11
>{ Set variable to todays date in julian day format }
>base membrs,5,lookup
>get member-file
>num 1
>def tdays,1,4,double
>item tdays,date,julian
>ext tdays=$today
>out tdays,link
>xeq
Warning: NUMRECS exceeded; some records not processed.
IN=2, OUT=1. CPU-Sec=1. Wall-Sec=1.
>in tdays
>def tdayascii,1,7,byte
>ext 'export MYJULDAY="'
>ext tdayascii=$edit(tdays,"zzzzzzz")
>ext '"'
>out setdate
>exit
IN=1, OUT=1. CPU-Sec=1. Wall-Sec=1.
SUPRTOOL/UXia/Copyright Robelle Solutions Technology Inc. 1981-2014.
(Version 5.6 Internal) WED, JAN 29, 2014, 11:55 AM Type H for help.
Build 11
>set varsub on
>in dates
>def diff,1,4,logical {note target is now logical}
>item a,date,ccyymmdd
>ext a
>ext diff=($MYJULDAY - $DAYS(A))
>list
>xeq
>IN dates (0) >OUT $NULL (0)
A = 20131213 DIFF = 47
>IN dates (1) >OUT $NULL (1)
A = 20131231 DIFF = 29
>IN dates (2) >OUT $NULL (2)
A = 20140201 DIFF = 0
>IN dates (3) >OUT $NULL (3)
A = 20140130 DIFF = 0
>IN dates (4) >OUT $NULL (4)
A = 20140115 DIFF = 14
IN=5, OUT=5. CPU-Sec=1. Wall-Sec=1.
>exit
Recent question about Phone Numbers and how to remove non-number characters from a byte container, raised some interesting solutions to normalizing phone numbers:
Considering the following data, you see that the phone numbers have all sorts of different formats.
>in myphone >list >xeq >IN myphone (0) >OUT $NULL (0) PHONENUM = #123.456.7890 >IN myphone (1) >OUT $NULL (1) PHONENUM = (123)567-1234 >IN myphone (2) >OUT $NULL (2) PHONENUM = (321).123.5678 IN=3, OUT=3. CPU-Sec=1. Wall-Sec=1.The steps in normalizing the data is to remove the non-numeric numbers:
>in myphone >set cleanchar "You can then use an edit mask to format it in the same way. You do need to redefine the field being edited with a define of the number with just the length of the phone number:" >clean "^0:^47","^58:^255" >def newphone,1,14 >ext phonenum=$clean(phonenum) >out newphone,link >xeq IN=3, OUT=3. CPU-Sec=1. Wall-Sec=1. >in newphone >list >xeq >IN newphone (0) >OUT $NULL (0) PHONENUM = 1234567890 >IN newphone (1) >OUT $NULL (1) PHONENUM = 1235671234 >IN newphone (2) >OUT $NULL (2) PHONENUM = 3211235678 IN=3, OUT=3. CPU-Sec=1. Wall-Sec=1.
>in newphone
>form
File: newphone (SD Version B.00.00) Has linefeeds
Entry: Offset
PHONENUM X14 1
Entry Length: 14 Blocking: 1
>def my,phonenum,10
>def targ,1,12
>ext targ=$edit(my,"xxx.xxx.xxxx")
>list
>xeq
>IN newphone (0) >OUT $NULL (0)
TARG = 123.456.7890
>IN newphone (1) >OUT $NULL (1)
TARG = 123.567.1234
>IN newphone (2) >OUT $NULL (2)
TARG = 321.123.5678
IN=3, OUT=3. CPU-Sec=1. Wall-Sec=1.
Translate "^65:^90" ext name=$translate(name)The extract command will change "Neil Armstrong" to "Neil Zrmstrong". We've also provided a sample translation table which will translate many readable characters to other readable characters just by using:
Translate TounreadYou can see an example below:
>get m-supplier >ext supplier-name >list stan >xeq May 02, 2014 8:08 Base STORE.TESTSD Set M-SUPPLIER Page 1 SUPPLIER-NAME Makita Canada Inc SKIL Power Tools Black & Decker IN=3, OUT=3. CPU-Sec=1. Wall-Sec=1. >get m-supplier >translate tounread >update >ext supplier-name=$translate(supplier-name) >xeq Update all records from the M-SUPPLIER dataset [no]: yes Warning: Using DBGET for the input records IN=3, OUT=3. CPU-Sec=1. Wall-Sec=1. >get m-supplier >ext supplier-name >list stan >xeq May 02, 2014 8:12 Base STORE.TESTSD Set M-SUPPLIER Page 1 SUPPLIER-NAME Npzxep Qpmpsp Wmr HYWZ Klhtc Gllod Poprz ; Rtrztc IN=3, OUT=3. CPU-Sec=1. Wall-Sec=1. >get m-supplier >translate toread >ext supplier-name=$translate(supplier-name) >list >list stan Warning: Previous >LIST options reset >xeq May 02, 2014 8:12 Base STORE.TESTSD Set M-SUPPLIER Page 1 SUPPLIER-NAME Makita Canada Inc SKIL Power Tools Black & Decker IN=3, OUT=3. CPU-Sec=1. Wall-Sec=1.This new feature is not meant as a method to encrypt data but does allow for you to quickly make test data unreadable.
I recently was asked how could you Obfuscate data in a byte type field AND be able to bring it back. Short of writing a $encrypt and $decrypt function, which may be in the works, I came up with the following:
Note the Product-desc field:/RUN SUPRTOOL.PUB.ROBELLE SUPRTOOL/Copyright Robelle Solutions Technology Inc. 1981-2014. (Version 5.6.12 Internal) MON, APR 7, 2014, 3:46 PM Type H for help. >BASE STORE,5,READER >GET M-PRODUCT >LIST >XEQ >GET M-PRODUCT (4) >OUT $NULL (0) PRODUCT-DESC = Skil 3/8" Variable Speed Drill PRODUCT-MODEL = #6523 PRODUCT-NO = 50531501 >GET M-PRODUCT (39) >OUT $NULL (1) PRODUCT-DESC = B&D Router PRODUCT-MODEL = #7613-04 PRODUCT-NO = 50522001 >GET M-PRODUCT (49) >OUT $NULL (2) PRODUCT-DESC = Skil Var. Sp. Auto-Scroll Saw PRODUCT-MODEL = #4560 PRODUCT-NO = 50533001 >GET M-PRODUCT (50) >OUT $NULL (3) PRODUCT-DESC = Skil 8 1/2" Circular Saw PRODUCT-MODEL = #5665 PRODUCT-NO = 50532501 >GET M-PRODUCT (52) >OUT $NULL (4) PRODUCT-DESC = B&D Cordless Screwdriver PRODUCT-MODEL = #9018-04 PRODUCT-NO = 50521001 >GET M-PRODUCT (103) >OUT $NULL (5) PRODUCT-DESC = Makita 8 1/4" Circular Saw PRODUCT-MODEL = #5008NB PRODUCT-NO = 50512501 >GET M-PRODUCT (146) >OUT $NULL (6) PRODUCT-DESC = B&D Variable Speed Jig Saw PRODUCT-MODEL = #7548-04 PRODUCT-NO = 50523001 >GET M-PRODUCT (164) >OUT $NULL (7) PRODUCT-DESC = Makita 1/2" Router PRODUCT-MODEL = #3601B PRODUCT-NO = 50512001 >GET M-PRODUCT (171) >OUT $NULL (8) PRODUCT-DESC = Makita 3/8" Var. Speed Drill PRODUCT-MODEL = #DP3730 PRODUCT-NO = 50511501 >GET M-PRODUCT (221) >OUT $NULL (9) PRODUCT-DESC = Skil Router PRODUCT-MODEL = #1835 PRODUCT-NO = 50532001 >GET M-PRODUCT (231) >OUT $NULL (10) PRODUCT-DESC = B&D 7 1/4" Circular Saw PRODUCT-MODEL = #5728 PRODUCT-NO = 50522501 >GET M-PRODUCT (241) >OUT $NULL (11) PRODUCT-DESC = B&D 3/8" Variable Speed Drill PRODUCT-MODEL = #P1149 PRODUCT-NO = 50521501 >GET M-PRODUCT (243) >OUT $NULL (12) PRODUCT-DESC = Makita 1" Jig Saw PRODUCT-MODEL = #4300 BV PRODUCT-NO = 50513001 IN=13, OUT=13. CPU-Sec=1. Wall-Sec=1.I made a simple translate file with the intended use being in a table with data. The key would be match to the original data and the Trans field would be what the data would become.
>in trans1 >list >xeq >IN TRANS1.NEIL.GREEN (0) >OUT $NULL (0) KEY = A TRANS = S >IN TRANS1.NEIL.GREEN (1) >OUT $NULL (1) KEY = B TRANS = T >IN TRANS1.NEIL.GREEN (2) >OUT $NULL (2) KEY = C TRANS = U >IN TRANS1.NEIL.GREEN (3) >OUT $NULL (3) KEY = D TRANS = V >IN TRANS1.NEIL.GREEN (4) >OUT $NULL (4) KEY = E TRANS = W >IN TRANS1.NEIL.GREEN (5) >OUT $NULL (5) KEY = F TRANS = X >IN TRANS1.NEIL.GREEN (6) >OUT $NULL (6) KEY = G TRANS = Y >IN TRANS1.NEIL.GREEN (7) >OUT $NULL (7) KEY = H TRANS = Z >IN TRANS1.NEIL.GREEN (8) >OUT $NULL (8) KEY = I TRANS = A >IN TRANS1.NEIL.GREEN (9) >OUT $NULL (9) KEY = J TRANS = B >IN TRANS1.NEIL.GREEN (10) >OUT $NULL (10) KEY = K TRANS = C >IN TRANS1.NEIL.GREEN (11) >OUT $NULL (11) KEY = L TRANS = D >IN TRANS1.NEIL.GREEN (12) >OUT $NULL (12) KEY = M TRANS = E >IN TRANS1.NEIL.GREEN (13) >OUT $NULL (13) KEY = N TRANS = F >IN TRANS1.NEIL.GREEN (14) >OUT $NULL (14) KEY = O TRANS = G >IN TRANS1.NEIL.GREEN (15) >OUT $NULL (15) KEY = P TRANS = H >IN TRANS1.NEIL.GREEN (16) >OUT $NULL (16) KEY = Q TRANS = I >IN TRANS1.NEIL.GREEN (17) >OUT $NULL (17) KEY = R TRANS = J >IN TRANS1.NEIL.GREEN (18) >OUT $NULL (18) KEY = S TRANS = K >IN TRANS1.NEIL.GREEN (19) >OUT $NULL (19) KEY = T TRANS = L >IN TRANS1.NEIL.GREEN (20) >OUT $NULL (20) KEY = U TRANS = M >IN TRANS1.NEIL.GREEN (21) >OUT $NULL (21) KEY = V TRANS = N >IN TRANS1.NEIL.GREEN (22) >OUT $NULL (22) KEY = W TRANS = O >IN TRANS1.NEIL.GREEN (23) >OUT $NULL (23) KEY = X TRANS = P >IN TRANS1.NEIL.GREEN (24) >OUT $NULL (24) KEY = Y TRANS = Q >IN TRANS1.NEIL.GREEN (25) >OUT $NULL (25) KEY = Z TRANS = R >IN TRANS1.NEIL.GREEN (26) >OUT $NULL (26) KEY = a TRANS = s >IN TRANS1.NEIL.GREEN (27) >OUT $NULL (27) KEY = b TRANS = t >IN TRANS1.NEIL.GREEN (28) >OUT $NULL (28) KEY = c TRANS = u >IN TRANS1.NEIL.GREEN (29) >OUT $NULL (29) KEY = d TRANS = v >IN TRANS1.NEIL.GREEN (30) >OUT $NULL (30) KEY = e TRANS = w >IN TRANS1.NEIL.GREEN (31) >OUT $NULL (31) KEY = f TRANS = x >IN TRANS1.NEIL.GREEN (32) >OUT $NULL (32) KEY = g TRANS = y >IN TRANS1.NEIL.GREEN (33) >OUT $NULL (33) KEY = h TRANS = z >IN TRANS1.NEIL.GREEN (34) >OUT $NULL (34) KEY = i TRANS = b >IN TRANS1.NEIL.GREEN (35) >OUT $NULL (35) KEY = j TRANS = c >IN TRANS1.NEIL.GREEN (36) >OUT $NULL (36) KEY = k TRANS = d >IN TRANS1.NEIL.GREEN (37) >OUT $NULL (37) KEY = l TRANS = e >IN TRANS1.NEIL.GREEN (38) >OUT $NULL (38) KEY = m TRANS = f >IN TRANS1.NEIL.GREEN (39) >OUT $NULL (39) KEY = n TRANS = g >IN TRANS1.NEIL.GREEN (40) >OUT $NULL (40) KEY = o TRANS = h >IN TRANS1.NEIL.GREEN (41) >OUT $NULL (41) KEY = p TRANS = i >IN TRANS1.NEIL.GREEN (42) >OUT $NULL (42) KEY = q TRANS = j >IN TRANS1.NEIL.GREEN (43) >OUT $NULL (43) KEY = r TRANS = k >IN TRANS1.NEIL.GREEN (44) >OUT $NULL (44) KEY = s TRANS = l >IN TRANS1.NEIL.GREEN (45) >OUT $NULL (45) KEY = t TRANS = m >IN TRANS1.NEIL.GREEN (46) >OUT $NULL (46) KEY = u TRANS = n >IN TRANS1.NEIL.GREEN (47) >OUT $NULL (47) KEY = v TRANS = o >IN TRANS1.NEIL.GREEN (48) >OUT $NULL (48) KEY = w TRANS = a >IN TRANS1.NEIL.GREEN (49) >OUT $NULL (49) KEY = x TRANS = p >IN TRANS1.NEIL.GREEN (50) >OUT $NULL (50) KEY = y TRANS = q >IN TRANS1.NEIL.GREEN (51) >OUT $NULL (51) KEY = z TRANS = r >IN TRANS1.NEIL.GREEN (52) >OUT $NULL (52) KEY = 1 TRANS = 9 >IN TRANS1.NEIL.GREEN (53) >OUT $NULL (53) KEY = 2 TRANS = 8 >IN TRANS1.NEIL.GREEN (54) >OUT $NULL (54) KEY = 3 TRANS = 7 >IN TRANS1.NEIL.GREEN (55) >OUT $NULL (55) KEY = 4 TRANS = 6 >IN TRANS1.NEIL.GREEN (56) >OUT $NULL (56) KEY = 5 TRANS = 5 >IN TRANS1.NEIL.GREEN (57) >OUT $NULL (57) KEY = 6 TRANS = 4 >IN TRANS1.NEIL.GREEN (58) >OUT $NULL (58) KEY = 7 TRANS = 3 >IN TRANS1.NEIL.GREEN (59) >OUT $NULL (59) KEY = 8 TRANS = 2 >IN TRANS1.NEIL.GREEN (60) >OUT $NULL (60) KEY = 9 TRANS = 1 >IN TRANS1.NEIL.GREEN (61) >OUT $NULL (61) KEY = / TRANS = * >IN TRANS1.NEIL.GREEN (62) >OUT $NULL (62) KEY = & TRANS = ! >IN TRANS1.NEIL.GREEN (63) >OUT $NULL (63) KEY = . TRANS = ) >IN TRANS1.NEIL.GREEN (64) >OUT $NULL (64) KEY = " TRANS = : >IN TRANS1.NEIL.GREEN (65) >OUT $NULL (65) KEY = _ TRANS = | >IN TRANS1.NEIL.GREEN (66) >OUT $NULL (66) KEY = - TRANS = } IN=67, OUT=67. CPU-Sec=1. Wall-Sec=1. rename trans1,translteI use this file in a table to do the translation:
>BASE STORE,1,WRITER >get m-product >TABLE MYTRANS,KEY,FILE,TRANSLTE,DATA(TRANS),HOLD There are 67 entries in MYTRANS >DEF A,PRODUCT-DESC[1],1,BYTE >DEF B,PRODUCT-DESC[2],1,BYTE >DEF C,PRODUCT-DESC[3],1,BYTE >DEF D,PRODUCT-DESC[4],1,BYTE >DEF E,PRODUCT-DESC[5],1,BYTE >DEF F,PRODUCT-DESC[6],1,BYTE >DEF G,PRODUCT-DESC[7],1,BYTE >DEF H,PRODUCT-DESC[8],1,BYTE >DEF I,PRODUCT-DESC[9],1,BYTE >DEF J,PRODUCT-DESC[10],1,BYTE >DEF K,PRODUCT-DESC[11],1,BYTE >DEF L,PRODUCT-DESC[12],1,BYTE >DEF M,PRODUCT-DESC[13],1,BYTE >DEF N,PRODUCT-DESC[14],1,BYTE >DEF O,PRODUCT-DESC[15],1,BYTE >DEF P,PRODUCT-DESC[16],1,BYTE >DEF Q,PRODUCT-DESC[17],1,BYTE >DEF R,PRODUCT-DESC[18],1,BYTE >DEF S,PRODUCT-DESC[19],1,BYTE >DEF T,PRODUCT-DESC[20],1,BYTE >DEF U,PRODUCT-DESC[21],1,BYTE >DEF V,PRODUCT-DESC[22],1,BYTE >DEF W,PRODUCT-DESC[23],1,BYTE >DEF X,PRODUCT-DESC[24],1,BYTE >UPDATE >ext a=$lookup(mytrans,a,trans) >ext b=$lookup(mytrans,b,trans) >ext c=$lookup(mytrans,c,trans) >ext D=$lookup(mytrans,D,trans) >ext E=$lookup(mytrans,E,trans) >ext F=$lookup(mytrans,F,trans) >ext G=$lookup(mytrans,G,trans) >ext H=$lookup(mytrans,H,trans) >ext I=$lookup(mytrans,I,trans) >ext J=$lookup(mytrans,J,trans) >ext K=$lookup(mytrans,K,trans) >ext L=$lookup(mytrans,L,trans) >ext M=$lookup(mytrans,M,trans) >ext N=$lookup(mytrans,N,trans) >ext O=$lookup(mytrans,O,trans) >ext P=$lookup(mytrans,P,trans) >ext Q=$lookup(mytrans,Q,trans) >ext R=$lookup(mytrans,R,trans) >ext S=$lookup(mytrans,S,trans) >ext T=$lookup(mytrans,T,trans) >ext U=$lookup(mytrans,U,trans) >ext V=$lookup(mytrans,V,trans) >ext W=$lookup(mytrans,W,trans) >ext X=$lookup(mytrans,X,trans) >xeq Update all records from the M-PRODUCT dataset [no]: YES Warning: Using DBGET for the input records IN=13, OUT=13. CPU-Sec=1. Wall-Sec=1.You can now not really read the data:
>GET M-PRODUCT >LIST >XEQ >GET M-PRODUCT (4) >OUT $NULL (0) PRODUCT-DESC = Kdbe 7*2: Nskbstew Kiwwv Drill PRODUCT-MODEL = #6523 PRODUCT-NO = 50531501 >GET M-PRODUCT (39) >OUT $NULL (1) PRODUCT-DESC = T!V Jhnmwk PRODUCT-MODEL = #7613-04 PRODUCT-NO = 50522001 >GET M-PRODUCT (49) >OUT $NULL (2) PRODUCT-DESC = Kdbe Nsk) Ki) Snmh}Kukhel Saw PRODUCT-MODEL = #4560 PRODUCT-NO = 50533001 >GET M-PRODUCT (50) >OUT $NULL (3) PRODUCT-DESC = Kdbe 2 9*8: Ubkunesk Ksa PRODUCT-MODEL = #5665 PRODUCT-NO = 50532501 >GET M-PRODUCT (52) >OUT $NULL (4) PRODUCT-DESC = T!V Uhkvewll Kukwavkbowk PRODUCT-MODEL = #9018-04 PRODUCT-NO = 50521001 >GET M-PRODUCT (103) >OUT $NULL (5) PRODUCT-DESC = Esdbms 2 9*6: Ubkunesk Kaw PRODUCT-MODEL = #5008NB PRODUCT-NO = 50512501 >GET M-PRODUCT (146) >OUT $NULL (6) PRODUCT-DESC = T!V Nskbstew Kiwwv Bby Kaw PRODUCT-MODEL = #7548-04 PRODUCT-NO = 50523001 >GET M-PRODUCT (164) >OUT $NULL (7) PRODUCT-DESC = Esdbms 9*8: Jhnmwk PRODUCT-MODEL = #3601B PRODUCT-NO = 50512001 >GET M-PRODUCT (171) >OUT $NULL (8) PRODUCT-DESC = Esdbms 7*2: Nsk) Kiwwv Vrill PRODUCT-MODEL = #DP3730 PRODUCT-NO = 50511501 >GET M-PRODUCT (221) >OUT $NULL (9) PRODUCT-DESC = Kdbe Jhnmwk PRODUCT-MODEL = #1835 PRODUCT-NO = 50532001 >GET M-PRODUCT (231) >OUT $NULL (10) PRODUCT-DESC = T!V 3 9*6: Ubkunesk Ksa PRODUCT-MODEL = #5728 PRODUCT-NO = 50522501 >GET M-PRODUCT (241) >OUT $NULL (11) PRODUCT-DESC = T!V 7*2: Nskbstew Kiwwv Drill PRODUCT-MODEL = #P1149 PRODUCT-NO = 50521501 >GET M-PRODUCT (243) >OUT $NULL (12) PRODUCT-DESC = Esdbms 9: Bby Ksa PRODUCT-MODEL = #4300 BV PRODUCT-NO = 50513001 IN=13, OUT=13. CPU-Sec=1. Wall-Sec=1.Then you can reverse the translation file key and translate fields in the table file and reverse them back: I reversed the translate fields:
>BASE STORE,1,WRITER >get m-product >TABLE MYTRANS,KEY,FILE,TRANSLTE,DATA(TRANS),HOLD There are 67 entries in MYTRANS >DEF A,PRODUCT-DESC[1],1,BYTE >DEF B,PRODUCT-DESC[2],1,BYTE >DEF C,PRODUCT-DESC[3],1,BYTE >DEF D,PRODUCT-DESC[4],1,BYTE >DEF E,PRODUCT-DESC[5],1,BYTE >DEF F,PRODUCT-DESC[6],1,BYTE >DEF G,PRODUCT-DESC[7],1,BYTE >DEF H,PRODUCT-DESC[8],1,BYTE >DEF I,PRODUCT-DESC[9],1,BYTE >DEF J,PRODUCT-DESC[10],1,BYTE >DEF K,PRODUCT-DESC[11],1,BYTE >DEF L,PRODUCT-DESC[12],1,BYTE >DEF M,PRODUCT-DESC[13],1,BYTE >DEF N,PRODUCT-DESC[14],1,BYTE >DEF O,PRODUCT-DESC[15],1,BYTE >DEF P,PRODUCT-DESC[16],1,BYTE >DEF Q,PRODUCT-DESC[17],1,BYTE >DEF R,PRODUCT-DESC[18],1,BYTE >DEF S,PRODUCT-DESC[19],1,BYTE >DEF T,PRODUCT-DESC[20],1,BYTE >DEF U,PRODUCT-DESC[21],1,BYTE >DEF V,PRODUCT-DESC[22],1,BYTE >DEF W,PRODUCT-DESC[23],1,BYTE >DEF X,PRODUCT-DESC[24],1,BYTE >UPDATE >ext a=$lookup(mytrans,a,trans) >ext b=$lookup(mytrans,b,trans) >ext c=$lookup(mytrans,c,trans) >ext D=$lookup(mytrans,D,trans) >ext E=$lookup(mytrans,E,trans) >ext F=$lookup(mytrans,F,trans) >ext G=$lookup(mytrans,G,trans) >ext H=$lookup(mytrans,H,trans) >ext I=$lookup(mytrans,I,trans) >ext J=$lookup(mytrans,J,trans) >ext K=$lookup(mytrans,K,trans) >ext L=$lookup(mytrans,L,trans) >ext M=$lookup(mytrans,M,trans) >ext N=$lookup(mytrans,N,trans) >ext O=$lookup(mytrans,O,trans) >ext P=$lookup(mytrans,P,trans) >ext Q=$lookup(mytrans,Q,trans) >ext R=$lookup(mytrans,R,trans) >ext S=$lookup(mytrans,S,trans) >ext T=$lookup(mytrans,T,trans) >ext U=$lookup(mytrans,U,trans) >ext V=$lookup(mytrans,V,trans) >ext W=$lookup(mytrans,W,trans) >ext X=$lookup(mytrans,X,trans) >xeq Update all records from the M-PRODUCT dataset [no]: YES Warning: Using DBGET for the input records IN=13, OUT=13. CPU-Sec=1. Wall-Sec=1.Now you can re-read the data since you've updated it and reversed the translation:
>GET M-PRODUCT >LIST >XEQ >GET M-PRODUCT (4) >OUT $NULL (0) PRODUCT-DESC = Skil 3/8" Variable Speed Drill PRODUCT-MODEL = #6523 PRODUCT-NO = 50531501 >GET M-PRODUCT (39) >OUT $NULL (1) PRODUCT-DESC = B&D Router PRODUCT-MODEL = #7613-04 PRODUCT-NO = 50522001 >GET M-PRODUCT (49) >OUT $NULL (2) PRODUCT-DESC = Skil Var. Sp. Auto-Scroll Saw PRODUCT-MODEL = #4560 PRODUCT-NO = 50533001 >GET M-PRODUCT (50) >OUT $NULL (3) PRODUCT-DESC = Skil 8 1/2" Circular Saw PRODUCT-MODEL = #5665 PRODUCT-NO = 50532501 >GET M-PRODUCT (52) >OUT $NULL (4) PRODUCT-DESC = B&D Cordless Screwdriver PRODUCT-MODEL = #9018-04 PRODUCT-NO = 50521001 >GET M-PRODUCT (103) >OUT $NULL (5) PRODUCT-DESC = Makita 8 1/4" Circular Saw PRODUCT-MODEL = #5008NB PRODUCT-NO = 50512501 >GET M-PRODUCT (146) >OUT $NULL (6) PRODUCT-DESC = B&D Variable Speed Jig Saw PRODUCT-MODEL = #7548-04 PRODUCT-NO = 50523001 >GET M-PRODUCT (164) >OUT $NULL (7) PRODUCT-DESC = Makita 1/2" Router PRODUCT-MODEL = #3601B PRODUCT-NO = 50512001 >GET M-PRODUCT (171) >OUT $NULL (8) PRODUCT-DESC = Makita 3/8" Var. Speed Drill PRODUCT-MODEL = #DP3730 PRODUCT-NO = 50511501 >GET M-PRODUCT (221) >OUT $NULL (9) PRODUCT-DESC = Skil Router PRODUCT-MODEL = #1835 PRODUCT-NO = 50532001 >GET M-PRODUCT (231) >OUT $NULL (10) PRODUCT-DESC = B&D 7 1/4" Circular Saw PRODUCT-MODEL = #5728 PRODUCT-NO = 50522501 >GET M-PRODUCT (241) >OUT $NULL (11) PRODUCT-DESC = B&D 3/8" Variable Speed Drill PRODUCT-MODEL = #P1149 PRODUCT-NO = 50521501 >GET M-PRODUCT (243) >OUT $NULL (12) PRODUCT-DESC = Makita 1" Jig Saw PRODUCT-MODEL = #4300 BV PRODUCT-NO = 50513001 IN=13, OUT=13. CPU-Sec=1. Wall-Sec=1.So while not really a sophisticated encryption of data it may allow you to mask some important data for test purposes.
:comment Step One: convert date to have a century field and a begining of year date in datefile def boy,1,4,double def century,1,4,double item boy,date,ccyymmdd item mydate,date,ccyymmdd ext mydate ext boy=$truncate(a / 10000) * 10000 + 0101 ext century=$truncate(a / 10000) out workfile,link xeq Step Two: Format and determine number of days since Beginning of Year. in workfile def newdate,1,4,double ext mydate ext boy ext century ext diff=$days(mydate) - $days(boy) + 1 ext newdate=(century * 1000) + ($days(a) - $days(boy) + 1) out convert,link xeqYou can see the results below including the starting date, you don't need to include all the fields in the result file:
>IN convert (0) >OUT $NULL (0) MYDATE = 20141213 BOY = 20140101 CENTURY = 2014 DIFF = 347 NEWDATE = 2014347 >IN convert (1) >OUT $NULL (1) MYDATE = 20131221 BOY = 20130101 CENTURY = 2013 DIFF = 355 NEWDATE = 2013355 >IN convert (2) >OUT $NULL (2) MYDATE = 20150321 BOY = 20150101 CENTURY = 2015 DIFF = 80 NEWDATE = 2015080 >IN convert (3) >OUT $NULL (3) MYDATE = 20100904 BOY = 20100101 CENTURY = 2010 DIFF = 247 NEWDATE = 2010247 IN=4, OUT=4. CPU-Sec=1. Wall-Sec=1.The basis of this was to take the current date and make up a second date and make up the Beginning Of Year date, and use $days and get the difference in days between the current date and the Beginning of Year. Thus the "Julian Date" or ccyyDDD, is the current century * 1000, plus the number of days difference (+1) from the beginning of the year.
One of the more common questions we get with respect to data and portions of data in a given field. Specifically, let's say you want to define the last four bytes of a given 20 byte field.
So with 20 bytes the last four bytes begins at byte 17:
12345669132135689071 Data 12345678901234567890 Byte PositionSo starting at offset 17 for a length of 4, the data extracted should be 9071.
>in atminfo >list >xeq >IN atminfo (0) >OUT $NULL (0) ATM-CARD-NUMBER = 12345669132135689071 IN=1, OUT=1. CPU-Sec=1. Wall-Sec=1. >in atminfo >def last-4,atm-card-number[17],4 >ext last-4 >list >xeq >IN atminfo (0) >OUT $NULL (0) LAST-4 = 9071 IN=1, OUT=1. CPU-Sec=1. Wall-Sec=1.So to deconstruct the define command, you define the new name associated with the field you want a portion of, and you define the starting offset and the length.
This is a line.would become:
Before Line This is a line. After LineNow normally in Qedit (host-based) this is extremely easy using a couple of tricks.
ch "This is a line."Before Line~This is a line.~After Line" @ divide "~" @ ch "~This is a line."This is a line." @ divide "~" @ ch "~After Line"After Line" @The use of the tilde character (I use this as it is typically unique) along with change and divide we end up getting a new line before and a new line after. However, the customer that was asking for this has Qedit for Windows only and Qedit server, which doesn't allow Host-based Qedit commands. Following is a script of a Qedit Scripting Language programming language, that will do just that:
-- Copyright 1996-2015 Robelle Solutions Technology Inc.
-- Version 1.10 May 21, 2015
sub create_at (line,column)
result = {};
result.line = line;
result.column = column;
return result;
endsub;
result = dialog("What string do you want to find?",1, "String to find.");
if result.button = 1 then
userString = result.enteredText;
endif
result = dialog("What line should be before?" ,1, "Line to add before string.");
if result.button = 1 then
lineBefore = result.enteredText;
endif
result = dialog("What line should be after?" ,1, "Line to add after string.");
if result.button = 1 then
lineAfter = result.enteredText;
endif
theFile = qedit.activefile;
findresult = theFile.find(string: userString);
repeat while findresult
row = theFile.lastfoundline - 1;
where = create_at(row,1);
theFile.insert(at: where, text: lineBefore);
row = theFile.lastfoundline + 1;
where = create_at(row,1);
theFile.insert(at: where, text: lineAfter);
findresult = theFile.find(string: userString);
endrepeat
theFile = dialog("End of Script");
Occasionally we get updates to customers e-mail addresses and often these e-mails have bad data in them. Over the past years I have tracked the bad data to be, Tab, Carriage Return or Line Feed. Luckily we have Suprtool to fix the data in very few commands:
base custdb,1,;
get d-custdata
clean "^9:^13"
if $findclean(e-mail-address)
update
ext e-mail-address=$clean(e-mail-address)
xeq
We specify Decimal Nine thru to Decimal 13, which is Tab thru to Line Feed, we use $findclean to find the entries and we update and run the extract of the e-mail address thru the $clean function and the data gets fixed and updated in one easy step.
There is a new feature in Suprtool 5.8.10 and higher which allows a user to add a number of months to a give date. Let's say you have to follow up with a customer in three months, a simple Suprtool task can do this:
base custdb,1,;
get customers
if custno="12345"
item follow-up-date,date,ccyymmdd
update
ext follow-up-date=$month(follow-up-date,+3)
xeq
This was done due to a direct request from one of our customers. One of the features of this is that if a month does not have the same number of days, Suprtool will find the closest date. For Example, if the +1 is invoked for a date that is say August 31st, the resulting date would be "September 31", which is of course incorrect. Suprtool will adjust the date to be a correct date of September 30th.