Question about only showing one date based on yes or no

[#1]

I have the following table:

What I want to show is EventID = 20, AlternateSchedule = no, and EventStartDate = 9/25/2014 and  EventID 780, AlternateSchedule = yes, and EventStartDate = 9/27/2014.

I was reviewing the firstsortedvalue command but not sure if this will work. 

Basically what happened is MasterScheduleID 1007 started out with two EventDates 9/25/2014 and 9/27/2014 from 3 - 4.  Then the time changed on the 9/27/2014 to 7 - 8 so I only want to show the 7 - 8 time. 

Does this makes sense?

Any help would be awesome.

David

[anbu1984]

Do you want to show only one row per MasterFSScheduleID based on Maxium EventStartDt and Eventstarttime?

[jagan · Accepted solution]

Hi David,

Try like this

Data:

LOAD

*

WHERE Flag = 1;

LOAD

*,

If(Peek('MasterScheduleID') <> MasterScheduleID, 1, If(Peek('AlternateSchedule') <> AlternateSchedule, 1,0)) AS Flag

FROM DataSource

ORDER By MasterScheduleID, EventStartDate, EventID;

Hope this helps you.

Regards,

Jagan.

[#4]

Hi Anbu,

That is correct.

[anbu1984]

In Script,

Load MasterFSScheduleID,Firstsortedvalue(EventStartDt,-EvntStrtTs) As EventStartDt,Firstsortedvalue(Eventstarttime,-EvntStrtTs) As Eventstarttime,

Firstsortedvalue(EventId,-EvntStrtTs) As EventId, ...

Group by MasterFSScheduleID;

Load MasterFSScheduleID, Date#(EventStartDt ,'M/D/YYYY') As EventStartDt, Date#(EventEndDt ,'M/D/YYYY') As EventEndDt , Time#(Eventstarttime,'hh:mm:ss TT') As Eventstarttime, Time#(EventEndtime,'hh:mm:ss TT') As EventEndtime, Timestamp#(EventStartDt & ' ' Eventstarttime ,'M/D/YYYY') EvntStrtTs,EventId From Table;