Find value for qty on last date for every item

[Anonymous]

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_id	item_id	location_id
1	Product A	GBJ
2	Product A	FIN
3	Product B	GBJ
4	Product C	GBJ

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

tx_id	tx_date	storage_id	start_qty	change_qty	end_qty
1	10/18/2018 12:48:23	1	0	2900	2900
2	11/8/2018 10:33:12	1	2900	2653	247
3	10/18/2018 12:48:56	3	0	1988	1988
4	11/8/2018 10:39:11	3	1988	198	1790
5	10/18/2018 12:48:27	4	0	280	280
6	11/8/2018 10:41:03	4	280	56	224
7	10/18/2018 12:48:02	2	0	0	0

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.

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

[sunny_talwar]

I can't think of an example where you might run into issues, but to be on the safe side... I would add those to my Aggr() function