Take input from two excel and formulate one

By -
This program takes input from two excel sheet and group into one sheet


DEFINE VARIABLE cal AS DECIMAL FORMAT "->>>,999,999.999".
DEFINE VARIABLE cal1 AS DECIMAL FORMAT "->>>,999,999.999".


/*DEFINE VARIABLE site1 LIKE sct_site.*/
DEFINE STREAM tero.

DEFINE TEMP-TABLE ttinv NO-UNDO
FIELD tt_part AS CHARACTER
FIELD tt_qty AS DECIMAL FORMAT "->>>,999,999.999"
FIELD tt_val AS DECIMAL FORMAT "->>>,999,999.999".



DEFINE TEMP-TABLE ttinv1 NO-UNDO
FIELD tttt_part1 AS CHARACTER
FIELD tttt_qty1 AS DECIMAL FORMAT "->>>,999,999.999"
FIELD tttt_val1 AS DECIMAL FORMAT "->>>,999,999.999".



INPUT FROM "C:\Documents and Settings\sachingaur\My Documents\gaur-27\pptrp07.csv".
REPEAT:
CREATE ttinv.
IMPORT DELIMITER "," ttinv.
END.
INPUT CLOSE.


INPUT FROM "C:\Documents and Settings\sachingaur\My Documents\gaur-27\xxinvrep.csv".
REPEAT:
CREATE ttinv1.
IMPORT DELIMITER "," ttinv1.
END.
INPUT CLOSE.

FOR EACH ttinv :
ASSIGN ttinv.tt_part = TRIM(ttinv.tt_part).
IF tt_part = '' THEN DELETE ttinv.
END.
FOR EACH ttinv1 :

ASSIGN tttt_part1 = TRIM(tttt_part1).
IF tttt_part1 = '' THEN DELETE ttinv1.
END.

OUTPUT TO "C:\Documents and Settings\sachingaur\My Documents\gaur-27\FINAL_SHEET.xls".
PUT UNFORMATTED
"Item Number" "~011"
"Quantity1 " "~011"
"Value1" "~011"
"Quantity2" "~011"
"Value2" "~011"
"val1-va12" "~011"
SKIP.
FOR EACH ttinv BREAK BY ttinv.tt_part :
cal = 0.
ACCUMULATE ttinv.tt_qty (TOTAL BY ttinv.tt_part).
ACCUMULATE ttinv.tt_val (TOTAL BY ttinv.tt_part).
IF LAST-OF(ttinv.tt_part) THEN DO:

IF (CAN-FIND(FIRST ttinv1 WHERE ttinv1.tttt_part1 = ttinv.tt_part)) THEN DO:
FOR EACH ttinv1 WHERE ttinv1.tttt_part1 = ttinv.tt_part BREAK BY ttinv1.tttt_part1:

cal1 = 0.
ACCUMULATE ttinv1.tttt_qty1 (TOTAL BY ttinv1.tttt_part1).
ACCUMULATE ttinv1.tttt_val1 (TOTAL BY ttinv1.tttt_part1).
IF LAST-OF(ttinv1.tttt_part1) THEN DO:
cal = ACCUM TOTAL BY ttinv.tt_part ttinv.tt_val.
cal1 = ACCUM TOTAL BY ttinv1.tttt_part1 ttinv1.tttt_val1.

PUT UNFORMATTED
ttinv.tt_part "~011"
ACCUM TOTAL BY ttinv.tt_part ttinv.tt_qty "~011"
cal /*ACCUM TOTAL BY ttinv.tt_part ttinv.tt_val*/ "~011"
ACCUM TOTAL BY ttinv1.tttt_part1 ttinv1.tttt_qty1 "~011"
cal1 /*ACCUM TOTAL BY ttinv1.tttt_part1 ttinv1.tttt_val1*/ "~011"
cal - cal1 "~011"
SKIP.


END.
END.
END.
ELSE DO:


PUT UNFORMATTED
ttinv.tt_part "~011"
ACCUM TOTAL BY ttinv.tt_part ttinv.tt_qty "~011"
ACCUM TOTAL BY ttinv.tt_part ttinv.tt_val "~011"
0 "~011 "
0 "~011"
ACCUM TOTAL BY ttinv.tt_part ttinv.tt_val "~011"
SKIP.
END.
END.
END.

FOR EACH ttinv1 WHERE ttinv1.tttt_part1 <> ' ' break BY ttinv1.tttt_part1 :
DEFINE VARIABLE i AS INTEGER NO-UNDO.
IF (CAN-FIND (FIRST ttinv WHERE ttinv.tt_part = ttinv1.tttt_part1)) THEN
DO:

END.
ELSE DO:
ACCUMULATE ttinv1.tttt_qty1 (TOTAL BY ttinv1.tttt_part1).
ACCUMULATE ttinv1.tttt_val1 (TOTAL BY ttinv1.tttt_part1).
IF LAST-OF(ttinv1.tttt_part1) THEN DO:

PUT UNFORMATTED
ttinv1.tttt_part1 "~011"
0 "~011"
0 "~011"
ACCUM TOTAL BY ttinv1.tttt_part1 ttinv1.tttt_qty1 "~011"
ACCUM TOTAL BY ttinv1.tttt_part1 ttinv1.tttt_val1 "~011"
0 - ACCUM TOTAL BY ttinv1.tttt_part1 ttinv1.tttt_val1 "~011"
SKIP.

END.
END.
END.

OUTPUT CLOSE.

Comments

Post a Comment

Popular posts from this blog

Progress 4GL interview questions for QAD technology

By -
1.        What is “No-undo” in the variable declaration? 2.        What are the different .pf parameters 3.        What are the different types of  locks in progress? Explain with example. If a record is locked Exclusively, can a shared lock be provided over the same? 4.        What are different type of procedures in progress? Can internal procedure call other internal procedure? If yes how? If no why no? Can external procedure call internal procedures? 5.        How is array defined in progress? What is the limit of extent for progress? Can we create 2 dimensional / multi dimensional arrays in progress / How? If no then how can we achieve the same? 6.        What are browses? 7.        What are queries? 8.        Can we call excel application from progress? How? 9.        What is the difference between can-find and find? 10.     What are the different types of triggers available in progress? Name few database level triggers. 11.     What are the different type
Read more

QAD interview questions for SE and Eb2 version

By -
1.       What is the difference between WO close and WO accounting close. 2.       What are the different types of transaction types in mfg-pro 3.       What is tag count when it is used 4.       Explain the purchase cycle 5.       What is the difference between the WO module and Repetitive  module. 6.       Explain the Payment cycle 7.       Explain the Sales Cycle 8.       What is a schedule order 9.       Explain GTM why and where it can be used 10.   How can u ensure that the supplier is given a schedule on a particular day of the week say only on Monday. 11.   What is MRP .What are the different types of MRP 12.   Can the  status of the the material can be changed 13.   What are financial transactions passed when you do voucher and payments. 14.   What are the transaction  passed when the payment is made 15.   The financial transaction passed when the sales  order is  updated. 16.   What are the primary input for MRP explain  in details 17.   Expl
Read more

Use of API in QAD EE

By -
/*Sample API Program to create currency in QAD EE*/ {us/mf/mfdtitle.i} {us/bbi/gplabel.i} {us/wb/wbrp01.i}         {us/gl/glsec.i} {us/gp/gpuid.i} {proxy/bcurrencydef.i } /* ======== FOR API ========= */ DEFINE VARIABLE viReturn                     AS INTEGER     NO-UNDO. DEFINE VARIABLE ilReturnDataset              AS LOGICAL     NO-UNDO. DEFINE VARIABLE ilPartialUpdate              AS LOGICAL     NO-UNDO. DEFINE VARIABLE icPartialUpdateExceptionList AS CHARACTER   NO-UNDO. DEFINE VARIABLE ocPrimaryKey                 AS CHARACTER   NO-UNDO. DEFINE VARIABLE ocRowid                      AS CHARACTER   NO-UNDO. DEFINE VARIABLE oiDraftInstance              AS INTEGER     NO-UNDO. DEFINE VARIABLE ocPrimaryKeyName             AS CHARACTER   NO-UNDO. DEFINE VARIABLE vho                          AS HANDLE. DEFINE VARIABLE oiReturnStatus               AS INTEGER     NO-UNDO. empty temp-table tCurrency. for first RoundingMethod no-lock where RoundingMethodCode = "
Read more