Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi.
I Have a straight table with 4 columns: Customer. Product, Update date and inventory (Quantity).
Not all the customers are updated daily and not all the products for a customer are updated in each update.
For each combination of customer & product, I need to show the latest update only.
How can this be achieved?
Regards,
Motty
Hi @sogloqlik
Try like below
Aggr(max([Update date], customer , product)
or
Max(Aggr(max([Update date], customer , product))
Hi @sogloqlik , here a sample of scripting for doing what you ask.
tell me if it works.
Data:
Load
Customer & ' | ' & Product as %_Key,
Customer.
Product,
[Update date],
inventory
From yoursource;
Data2:
Load
%_Key,
max([Update date]) as [Max Update date]
Resident Data
group By %_Key;
Hi.
Thx for your answer
Cant do it in the script. must be in the chart itself.
Hi @sogloqlik
Try like below
Aggr(max([Update date], customer , product)
or
Max(Aggr(max([Update date], customer , product))
@sogloqlik try mayils suggestion.
i would actually suggest a variation of @QFabian s suggestion. in the script using max create a flag which indicates the latest records for your combination.
then use that flag in set analysis. this would be most efficient and flexible way. aggr will work but can be a little heavy on processing.
Data:
Load
Customer & ' | ' & Product as %_Key,
Customer,
Product,
[Update date],
inventory
From yoursource;
left join (Data)
Load
%_Key,
max([Update date]) as [Max Update date]
'Y' as LatestFlag
Resident Data
group By %_Key;
Combination of that on the Date and first sorted value on the Qty Solved the problem.
Thx.