Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
skzwane01
Contributor III
Contributor III

Pulling the Latest/ Max Record based

Hi,

Please see below.

Below I have inventory data .Doc Type IC means its Inventory Completions which means its in stock. BI means its Invoiced, meaning it has been sold.

 I would like to get the Lot Numbers which are in stock (IC) but have no BI.

 So this example below means it’s been sold already hence the Negative value for Doctype BI. I want to create an expression which counts the Lot Numbers which are IC but have no BI. So I want to, in essence pull the latest record per Lot Number.

In the example below the max record would be the record of the 2018/05/02.

LocationCalDateItemCodeLotNumberDocTypeQty
FG22018/04/01011P02125080017N0001/1AIC0.696
FG22018/04/01011P02125080017N0001/1AIB0
FG22018/05/02011P02125080017N0001/1ABI-0.696

 

Hope this makes sense ?

 

 

3 Replies
vishus913
Partner - Creator
Partner - Creator

Maybe this

Count(distinct{<DocType={'IC'}>*<DocType -={'BI'}>} ItemCode)

 

 

sunny_talwar

You want to Count(LotNumber) or do you want to get max record? I am confused as to what your final output needs to look like. Would you be able to clarify?
skzwane01
Contributor III
Contributor III
Author

Hi,

 

Thank you for your assistance, the above response was closer to what I was hoping to achieve. I added an exclusion on the Set expression as shown below.

 

Count({<DocType ={'IC'},LotNumber *= E({<DocType = {'BI','BH'}>} LotNumber) >} LotNumber)