Getting a state on the basis of timestamp

[ruma_barman]

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

[tresesco]

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)

[ruma_barman]

Hi Tresesco,

Still,it's not giving required output on selection.

Regards,

Ruma

[thannila]

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;

[Sue_Macaluso]

Is this for QlikView or Sense? I would like to move it into the product forum. This one is for non product discussions. Thanks