Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Uploads getting stuck in the virus scanner. We are investigating.
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Getting a state on the basis of timestamp

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

 

 

4 Replies
Highlighted
MVP
MVP

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)

 

Highlighted
Creator
Creator

Hi Tresesco,

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

Regards,

Ruma

 

 

Highlighted
Creator
Creator

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;

Highlighted
Community Manager
Community Manager

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