Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
faroukfrk
New Contributor III

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

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  CaptureQlikCommunity.PNG

 

 

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

)t  

order by "FQST_Date_Stock" DESC

 

Thank you

3 Replies

Re: Last Row VALUE based on Distinct fields

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
faroukfrk
New Contributor III

Re: Last Row VALUE based on Distinct fields

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

faroukfrk
New Contributor III

Re: Last Row VALUE based on Distinct fields

@Michael_Tarallo  do you have any idea ?!

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