Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a requirement like if I select a monthyear , the contract_id and its corresponding maximum state should come.
E.g:
Load * Inline
[
ContractID, Event_Date, Contract_State
1201, 09-07-2018 05:53, QUALIFY
1201, 09-07-2018 06:18, FIELD VISIT
1201, 09-07-2018 06:23, CREDIT
1201, 09-07-2018 06:27, BOOKED
1202, 09-08-2018 06:33, QUALIFY
1202, 09-08-2018 06:46, FIELD VISIT
1202, 09-10-2018 06:52, CREDIT
1203, 09-10-2018 06:53, BOOKED
];
Now,
If July is selected,O/P should be:
ContractID, ContractState
1201,BOOKED
If Aug is selected,O/P should be:
ContractID, ContractState
1202,FIELD VISIT
If Oct is selected,O/P should be:
ContractID, ContractState
1202,CREDIT
1203,BOOKED
Thanks and Regards,
Ruma
Try using calendar date instead of event date which is a string field. Also use negative symbol before date parameter in firstsortedvalue() for max date value.
FirstSortedValue(Contract_State,-Calender_Date)
Hi Tresesco,
Still,it's not giving required output on selection.
Regards,
Ruma
Data:
Load * Inline
[
ContractID, Event_Date, Contract_State
1201, 09-07-2018 05:53, QUALIFY
1201, 09-07-2018 06:18, FIELD VISIT
1201, 09-07-2018 06:23, CREDIT
1201, 09-07-2018 06:27, BOOKED
1202, 09-08-2018 06:33, QUALIFY
1202, 09-08-2018 06:46, FIELD VISIT
1202, 09-10-2018 06:52, CREDIT
1203, 09-10-2018 06:53, BOOKED
];
DATA1:
LOAD *,Timestamp#(Event_Date,'DD-MM-YYYY hh:mm') as EVENT Resident Data;
drop table Data;
Data2:
load *,month(EVENT) as Month Resident DATA1;
drop table DATA1;
Right join(Data2)
Data3:
load max(EVENT) as EVENT,Month,ContractID
resident Data2 group by Month,ContractID;