[SOLVED] Transforming a file inserting a new record
I have a file containing client no, order no and order line no. This has to be transformed to a new file such that for each client no a new line has to be inserted for the first order.
Input
Client No Order No Order Line no
12345 A001 0001
12345 A001 0002
12345 A001 0003
12345 A003 0001
12345 A003 0002
23456 A002 0001
23456 A002 0002
34567 A005 0001
34567 A006 0001
Desired Output:
Client No Order No Order Line no
12345 A001 0001
12345 A001 0002
12345 A001 0003
12345 A001 9999
12345 A003 0001
12345 A003 0002
23456 A002 0001
23456 A002 0002
23456 A002 9999
34567 A005 0001
34567 A005 9999
34567 A006 0001
Can anyone help me out in adding a new record one for the first order of each client.
Thanks, ShrikantThis message has been edited. Last edited by: Kerry,
FOCUS 7.2.3 Platform: IBM system Z9 Business class O/P formats: Flat files, excel and CSV files
November 04, 2008, 01:14 AM
<JG>
table file print hold end -run filedef hold (apppend -run table file sum compute onhold set holdlist hold
everything you need is there
November 04, 2008, 03:47 AM
FrankDutch
Shrikant
I do not understand the question. Your examples shows a 3 new lines but not on places where your question would have them.
3 client, 5 orders, so five new lines ??
Is this a reporting question or a maintain?
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
November 04, 2008, 04:45 AM
Shrikant
Thanks JG and Frank for your replies.
Frank - As mentioned, I need to have a extra line only for the first order of any client. That means the number of new lines inserted should be equal to distint clients in the input file and it has to be inserted for the first order of that client.
FOCUS 7.2.3 Platform: IBM system Z9 Business class O/P formats: Flat files, excel and CSV files
November 04, 2008, 04:52 AM
GamP
Do it as JG already indicated in his post. You have everything in place. So first table the input file entirely, printing all fields you need. Then table the file again, only now do a sum and a by for clientnr and a by lowest 1 for your ordernumber and compute the new orderline number. BE sure the result is exactly the same as the first table and append it to the first table. Then table the resulting file in the order you need. You only need to translate these hints into real code.
Hope this helps....
GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
November 04, 2008, 12:49 PM
<JG>
quote:
filedef hold (apppend
Spot the mistake
should be
filedef hold (append
November 05, 2008, 09:25 AM
Danny-SRL
Shrikant,
Right! Only for the first order of each client:
TABLE FILE SHRIKANT
PRINT CLIENTNO ORDERNO LINENO
ON TABLE HOLD AS ADDREC FORMAT ALPHA
END
FILEDEF ADDREC DISK ADDREC.FTM (APPEND
SET HOLDLIST=PRINTONLY
TABLE FILE SHRIKANT
SUM FST.ORDERNO
COMPUTE NEWLINE/I4L=9999;
BY CLIENTNO
ON TABLE SAVE AS ADDREC
END
TABLE FILE ADDREC
PRINT LINENO
BY CLIENTNO
BY ORDERNO
END
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
November 05, 2008, 03:46 PM
Waz
With FILEDEF, it is always good practice to use a FILEDEF ddname DISK filename after you have finished appending.