Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I want to show the closing stock by warehousecode and productcode and date wise.
But when i try to sum it, if there is only one date, i am getting the closing stock as expected. But if there are multiple dates available i need to only the last value as closing stock for that particular data but i am getting all the data.
Please have a look at the attached sample qvw.
Thank you.
Hi viresh,
how do you know which one the last value is.
If there is a timestamp or a Transaction ID you can try an Expression like this
sum({<StockDateTime={'=max(StockDateTime)'}>}ClosingStock)
Hi, yes even i tried the below expression and it looks good.
=sum({<StockDate={$(=max(StockDate))}>}ClosingStock)
how we can make this in the edit script instead front end.
Thank you.
add this to your script
join load
ProductKey,
StockDate,
LastValue(ClosingStock) as LastClosingStock
Resident data
Group by ProductKey, StockDate
Or, I guess FirstSortedValue - script function ‒ QlikView would be better way.
whats the difference? Should be the same in both expressions.
i tried lastvalue but not getting the data as expected. Need to check with FirstSortedValue
If you meant - firstvalue() VS firstsortedvalue(), then THERE ARE differences.
FirstValue() returns the value that was loaded first from the records ... // depends on load order
FirstSortedValue() returns the value from the expression specified in value that corresponds to the result of sorting the sort_weight argument.... // depends on the parameter you pass ; in this case it would a date field.
thats weird. i tested it and i got the right values.
ok try FirstSortedValue. I think you Need to set the rank Parameter to -1 to get the last value
regards
tim
Hi,
I used the below script but still it is combining the all values.
Directory;
LOAD ProductKey,
WarehouseCode,
ProductCode,
StockDate,
OpeningStock,
TransactionQty,
firstsortedvalue(ClosingStock,StockDate) as ClosingStock
FROM
[InvCalculation.QVD]
(qvd)
group by ProductKey,
WarehouseCode,
ProductCode,
StockDate,
OpeningStock,
TransactionQty;
Let me if i missed out anything.