Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 get the desired output
Hi dude,
i suggest you to create one fact table.
If you need any information from purchases, sales and adjustment you can use applymap to get it.
You need only one table with quantity, inventory name, sign, code etc. to get the inventory balances.
Then you can use rangesum + peek to get the balance directly from the script.
You can manage time formats with time.. for example:
time(time#( A, 'hh.mm' ))