Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Can someone help me to achieve this?
This is the template I need to get.
Doc Number contains -> Invoice Number and Doc Sequence Value as in order
Doc Type contains -> Invoice, Receipts, Credit Memo and Debit Memo as Types
Doc Date contains -> Invoice Date and Apply Date
Data modeling for the above-mentioned columns is below.
Black colored -> Doc Number Column
Blue Colored -> Doc Type
Red Colored -> Doc Date
Green lines are links in between these tables.
Could someone provide a way to achieve this template (1st image) using these tables?
Can someone help me to achieve this? @sunny_talwar @Gysbert_Wassenaar @rubenmarin1
Hi,this needs some time and merging data step by step.
I would start with RA_CUSTOMER_TRX, loading CUSTOMER_TRX_ID and the squared fields, left join data from AR_RECIVABLE_APPLICATIONS and left join AR_CASH_RECEIPTS.
Having that megerd you can create an auxiliar table that for each data does the calculation to obtain the balance, using a group by, and create a mapping table to assign that value.
And lastly load the final tabla , loading first the values from type=invoice, and craeting the _Balance field using applymap, then concatenate the receipts.
Load order of reow looks that there is always first sorted by date and then doc. date, if you need another sorting you can add a calulated field by concatenating tables.
Hello,
Concatenate is the right way to go
LOAD Name,
Firstname,
Concat (Code, ', ') as Code,
Valid
FROM
Group By Name, Firstname, Valid;