8 Replies Latest reply: Nov 9, 2018 9:18 AM by Riken Ardian RSS

    Find value for qty on last date for every item

    Riken Ardian

      I have this warehouse database using MariaDB and want to show all stock for every item in every warehouse in Qliksense.

       

        This is list_item_storage table to show relational from product to warehouse

      storage_iditem_idlocation_id
      1Product AGBJ
      2Product AFIN
      3Product BGBJ
      4Product CGBJ

       

       

      And this is list_item_tx table to show all in and out transaction related to every product in all warehouse.

         

      tx_idtx_datestorage_idstart_qtychange_qtyend_qty
      110/18/2018 12:48:231029002900
      211/8/2018 10:33:12129002653247
      310/18/2018 12:48:563019881988
      411/8/2018 10:39:11319881981790
      510/18/2018 12:48:2740280280
      611/8/2018 10:41:03428056224
      710/18/2018 12:48:022000

       

       

       

       

      I want to make something like below. But I can't get the right value to show the stock value. According to DB Admin, the logic is to grab all of the storage_id for every product in list_item_storage table. And then grab the storage_id with the latest date (tx_date) then show the end_qty in list_item_tx.

      Capture.JPG

       

      My problem is I can't get the right value for the latest stock for every product in every warehouse.

       

      I tried several expression such as:

      sum({<tx_date={"$(=max(tx_date))"}>}end_qty)

      sum(if(tx_date=Max(tx_date),end_qty))

      FirstSortedValue(if({<tx_date={"$(=max(tx_date))"}>}end_qty))

      and even using Aggregation functions such as ONLY or MaxStrings and none of them succeed. Most of it ended with the result of showing NULL or showing only the last row value of the table.

       

      Please help. Thanks in advance