Data Model / Table Issue

azmeerrehan — Fri, 02 Nov 2018 15:05:45 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 get the desired output

Re: Data Model / Table Issue

captain89 — Thu, 13 Dec 2018 13:48:07 GMT

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' ))

topic Data Model / Table Issue in QlikView

Data Model / Table Issue

Re: Data Model / Table Issue