Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am working on Stock Analysis.
Where a stock is scanned on dealers in a month.
Data looks like below
Account_ID Date Stockqty
1 23-07-2017 10
2 24-07-2017 12
2 25-07-2017 13
2 25-07-2017 2
1 27-07-2017 9
Expected Result
2 25-07-2017 15
1 27-07-2017 9
whenever I will check for stock it should provide me the stock scanned on the maximum date of each dealer.
Please suggest the way to get this
For now what I have done is aggr(Max(Date),Account_ID) to get the maximum date corresponding to each dealer but it is not giving me the Required Quantity!
Hi,
Table:
LOAD * Inline [
AccountId,Date,Stockqty
1,23-07-2017,10
2,24-07-2017,12
2,25-07-2017,13
2,25-07-2017,2
1,27-07-2017,9];
Join LOAD AccountId,Max(Date) as Date,1 as Flag
Resident Table Group By AccountId;
NoConcatenate
LOAD AccountId,Max(Date) as Date,Sum(Stockqty) as Stockqty
Resident Table Where Flag=1
Group By AccountId;
Drop Table Table;
Regards,
Antonio
Hi,
Table:
LOAD * Inline [
AccountId,Date,Stockqty
1,23-07-2017,10
2,24-07-2017,12
2,25-07-2017,13
2,25-07-2017,2
1,27-07-2017,9];
Join LOAD AccountId,Max(Date) as Date,1 as Flag
Resident Table Group By AccountId;
NoConcatenate
LOAD AccountId,Max(Date) as Date,Sum(Stockqty) as Stockqty
Resident Table Where Flag=1
Group By AccountId;
Drop Table Table;
Regards,
Antonio
Hi swetha,
Try this,
=SUM(IF(Date_1=AGGR(nodistinct MAX(Date_1),Account_ID),Stockqty))
Regards,
Alternatively you can try this:
Dimensions:
Account_ID
aggr(Max(Date), Account_ID)
Expressions:
=Sum({$<Date={"=Max(Date)"}>}Stockqty)
Do as follow:
Load date(max(date)) as date,Account_ID,sum(Stockqty) as Stockqty group by
Account_ID
;
load Account_ID,date(Date#(Date,'DD-MM-YYYY')) as date, Stockqty Inline [
Account_ID, Date, Stockqty
1 , 23-07-2017 , 10
2 , 24-07-2017 , 12
2 , 25-07-2017 , 13
2 , 25-07-2017, 2
1 , 27-07-2017, 9
];
result:
Hi Antonio,
Can you please send me the sample qvf of this
See Attachment
Hi Antonio,
Thanks a lot for helping. Solution worked!
Hi Shweta,
Kindly find the qvf file as per your requirement.
Thanks & regards,
Sumit Kumar Srivastava