The objective is to build an inventory product/location dashboard. The data sources are a AuditLog.qvd which contains information regarding every transaction that changes the inventory balance. The transactions come from three possible sources, Adjustments (Adjustments.qvd), Sales (Sales.qvd) and Purchases(Purchases.qvd).
The composite key between the tables will be made from the following fields
AuditLog Adjustments Sales Purchases
KWCOMP XFCOMP SKCOMP ICOMPANY
KWLOC XFFOM/XFTO ALPHA8 IALPHA10
KWCOMM XFCOMM COMMCODE*1 ICOMMCODE
AUSER XFCRTBY SENTUSR ISENTUSR
AUDATE XFCRTDT SENTDATE ISENTDATE
AUTIME XFCRTTM SENTTIME ISENTTIME
*Note1
Please use the following to convert the Sales COMMCODE value
AuditLog KWCOMM Sales COMMCODE
102 208
The dashboard shall consist of a selection for a Calendar Month Year, Calendar Date, KWLOC, KWCOMM and transaction type.
There shall be a table of the Audit Transactions in chronological sequence descending with the ability to select one or more rows (See attachment)
There shall be a detail tables for the Adjustments, Sales and Purchases.
The challenge is the difference in the time values between some of the tables. Some tables have the HHMMSS where others only have HHMM. In addition the time value could be off by a minute or more. For example the Audit table may have 13:04 and the Sales table will have 13:05.
How can I build this data model to get the desired results
