Interval

[#1]

Hi Experts,

Can you please correct my equation. I am not getting how to apply flag in interval function. Since i want to take the difference b/w two timestamps with diffrent flags.

interval(if(Flag='no',Timestamp(min(Datetime,2)))-if(Flag='no',Timestamp(min(Datetime,2)))) as Difference

Thanks

[#2]

I think it looks ok. What happends? You have the same flag in both times btw.

[jonathandienst]

Hi

I think your expression is incorrect as it will only return a null (if flag <> 'no') or a zero. What are you trying to do in the expression?

Regards

Jonathan

[#4]

Hi ,

The two flags are different. Sorry earlier it was a mistake.

interval(if(Flag='no',Timestamp(min(Datetime,2)))-if(Flag='yes',Timestamp(min(Datetime,2))))) as Difference

Now after loading i am getting invalid syntax error. But looks ok in script. That means no red lines visible.

[tresesco]

Hi,

It seems that your logic is itself not very logical. One record can have one flag 'yes' or 'no'. Therefore you can't get difference between them for a single record (or to be precise, the difference is always going to be the one the Datetime field itself because the other one is always ZERO ...if one is true then the other one is false).

[#6]

Well, I assumed that he used a Group by. Otherwise it doesnt make any sense.

Try this

interval(Timestamp(min(if(Flag='no',Datetime),2))-Timestamp(min(if(Flag='yes',Datetime),2))) as Difference

[#7]

Engineer	WorkOrder	Flag	Datetime
Ganesh	100	no	01/01/2013 12:10
Ganesh	100	no	02/01/2013 12:20
Ganesh	100	yes	03/01/2013 12:40
Ganesh	100	yes	04/01/2013 13:10
Ganesh	100	no	05/01/2013 14:30
Ganesh	100	no	06/01/2013 12:10
Ganesh	100	yes	07/01/2013 12:10
Ganesh	100	yes	08/01/2013 12:10
Ganesh	100	yes	09/01/2013 12:10
Ganesh	100	yes	10/01/2013 12:10

Hi,

I want to take the difference b/w 06/01/2013 12:10 and 04/01/2013 13:10

In this scenaro i have used interval function...Let me know if any more info needed for query.

[#8]

Hi,

You are right I am using group by here.

Can you please tell me that how to take difference b/w below.

Whatever u given answer that is correct. But I am looking something else like below bold labelled difference.

Ganesh	100	no	01/01/2013 12:10
Ganesh	100	yes	02/01/2013 12:20
Ganesh	100	yes	03/01/2013 12:40
Ganesh	100	yes	04/01/2013 13:10
Ganesh	100	no	05/01/2013 14:30
Ganesh	100	no	06/01/2013 12:10
Ganesh	100	yes	07/01/2013 12:10
Ganesh	100	yes	08/01/2013 12:10
Ganesh	100	yes	09/01/2013 12:10
Ganesh	100	yes	10/01/2013 12:10

07/01/2013 12:10 - 05/01/2013 14:30

[#9]

Ok, but what is your logic? I mean why are you comparing exactly that two dates? You have to tell Qlikview which dates you are comparing.

[#10]

I am implenting the same scenario in my assignment. There are different field names are there in real assignments. Here I did data mockup and calculating accordingly as I cant share business data or field.

In qlikview I can be able to take the diffrence b/w two dates withing same field with first minimum. But not on full extent. I mean to say , as currently I want to take the difference b/w (date)yes after no and (date)no after yes. This one is really difficult for me. So need Community help.