Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Question about only showing one date based on yes or no

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

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

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.

View solution in original post

4 Replies
anbu1984
Master III
Master III

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

jagan
Luminary Alumni
Luminary Alumni

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.

Not applicable
Author

Hi Anbu,

That is correct.

anbu1984
Master III
Master III

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;