DATA MODEL ISSUE

azmeerrehan — Wed, 24 Oct 2018 15:20:59 GMT

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

topic DATA MODEL ISSUE in QlikView

DATA MODEL ISSUE