Last Row VALUE based on Distinct fields - Similar to "select distinct on (filed1,filed2)" SQLQuery

faroukfrk — Sat, 16 Nov 2024 21:06:16 GMT

Hello, Guys Hope you all doing well,

I have a problem with the sum of stock quantities

first of all, here is the header of the main columns in the fact table "Stock Quantity "

"FQST_Stock_Id" , "ENT_Entreprise_Id", "STR_Magasin_Id", "PRD_Produit_Id","EMPL_Emplacement_Id","FQST_Date_Stock" and "FQST_Quantite_Produit", "FQST_Prix_Public"

I want to have the stock value at a selected date for all the products at any enterprise any emplacement (location) and any magazine(store)

I have made this set analysis expression however it gives me just the sum of last date (max date):

Sum({<date={"$(=max(FQST_Date_Stock))"},[TypeDate]={'DateStock'},[FQST_Type]={'StockInterne'}>}
FQST_Quantite_Produit*FQST_Prix_Unitaire_Quant)

the picture joined with the question may explain more by default and without selecting any date

the result of the measure is the sum of rows in green based on a distinct value of the fields in yellow we get the sum of last values

so if you may help me to get the stock value by fetching the last distinct value of a row composed of the filed mentioned above

PS: it is near in meaning to the SQL instruction "Distinct On"

and it is the result of this SQL query

select * from

(SELECT DISTINCT ON ("ENT_Entreprise_Id", "STR_Magasin_Id","PRD_Produit_Id", "EMPL_Emplacement_Id") t1.*

FROM "BI"."Test" t1

order by "PRD_Produit_Id", "EMPL_Emplacement_Id","ENT_Entreprise_Id","STR_Magasin_Id",

"FQST_Date_Stock" DESC

order by "FQST_Date_Stock" DESC

Thank you

Re: Last Row VALUE based on Distinct fields

Anil_Babu_Samineni — Sun, 07 Apr 2019 12:58:11 GMT

The image rows are blur, Could you please specify the few rows and expected one?

Re: Last Row VALUE based on Distinct fields

faroukfrk — Sun, 07 Apr 2019 16:41:57 GMT

the first table is the input and the green colored rows in the second table are the output

You may zoom on the picture to see it clearly @Anil_Babu_Samineni

you may also take a look at the SQL Query i added at the end it gives the result i want to have

Re: Last Row VALUE based on Distinct fields

faroukfrk — Mon, 08 Apr 2019 07:45:11 GMT

@Michael_Tarallo do you have any idea ?!

how to make a "select distinct on (filed1,filed2)" withing qlik sense using chart functions

topic Re: Last Row VALUE based on Distinct fields in App Development

Last Row VALUE based on Distinct fields - Similar to "select distinct on (filed1,filed2)" SQLQuery

Re: Last Row VALUE based on Distinct fields

Re: Last Row VALUE based on Distinct fields

Re: Last Row VALUE based on Distinct fields