topic Re: Calculate data_duration in QlikView

Calculate data_duration

martinqlik — Sat, 03 Sep 2016 16:14:58 GMT

Hello folks,

I'd like to get a interval btw two dates(slide enter and slide exit), but it's not working. I must calculate dt_enter - dt_exit in the load script. How can I make this?

Thank y'all.

Example:

Slide_Enter:

Load DISTINCT value_id,

RIGHT(event_date,8) as dt_enter,

category as category_enter

FROM archive.qvd (qvd)

Where category = 'slideEnter';

LEFT JOIN(Slide_Enter)

LOAD DISTINCT value_id,

RIGHT(event_date,8) as dt_exit,

category as category_exit

FROM archive.qvd (qvd)

Where category = 'slideExit';

Re: Calculate data_duration

sunny_talwar — Sat, 03 Sep 2016 17:27:32 GMT

Would you be able to share a sample of what you trying to do? May be this

Slide_Enter:

Load DISTINCT value_id,

RIGHT(event_date,8) as dt_enter,

category as category_enter

FROM archive.qvd (qvd)

Where category = 'slideEnter';

LEFT JOIN(Slide_Enter)

LOAD DISTINCT value_id,

RIGHT(event_date,8) as dt_exit,

category as category_exit

FROM archive.qvd (qvd)

Where category = 'slideExit';

FinalTable:

LOAD *,

dt_exit - dt_enter as dt_diff

Resident Slide_Enter;

DROP Table Slide_Enter;

Re: Calculate data_duration

martinqlik — Sat, 03 Sep 2016 17:48:07 GMT

It's something like that. But I want data in format MM:SS

Could you check this QVD out?

Re: Calculate data_duration

sunny_talwar — Sat, 03 Sep 2016 19:00:10 GMT

May be like this:

FinalTable:

LOAD *,

Interval(dt_exit - dt_enter, 'mm:ss') as dt_diff

Resident Slide_Enter;

Re: Calculate data_duration

sunny_talwar — Sat, 03 Sep 2016 19:06:37 GMT

Check the attached may be

Re: Calculate data_duration

martinqlik — Sat, 03 Sep 2016 19:26:28 GMT

It's working perfectly. Thank you Sunny.

Last Question: It's any way to this dt_diff do not be negative? My main purpose is to show in how long a slide can be shown. That's why it can't be negative.

Re: Calculate data_duration

sunny_talwar — Sun, 04 Sep 2016 10:24:37 GMT

What do you want it to be when it is negative? 0 or null???

May be try this:

FinalTable:

LOAD *,

Interval(dt_exit - dt_enter, 'mm:ss') as dt_diff,

Interval(If(dt_exit - dt_enter > 0, dt_exit - dt_enter, 0), 'mm:ss') as dt_diff_zero,

Interval(If(dt_exit - dt_enter > 0, dt_exit - dt_enter, Null()), 'mm:ss') as dt_diff_null

Resident Slide_Enter;

Re: Calculate data_duration

martinqlik — Sun, 04 Sep 2016 16:29:23 GMT

Sunny, you're wright.

But, it's any way to make this to do not be negative? Let me make myself clear.

There is any other function to sum these values that do not make the result of sum in negative? Interval function is correct and working well, but I think it make results in negative format, what isn't useful for me.

Re: Calculate data_duration

sunny_talwar — Tue, 06 Sep 2016 21:44:15 GMT

Not sure I understand Martin, you don't want to convert negative into positives? Use fabs() function for that

Interval(fabs(dt_exit - dt_enter), 'mm:ss') as dt_diff,

Re: Calculate data_duration

martinqlik — Fri, 09 Sep 2016 13:06:55 GMT

Sunny, I finally figure out what's my problem.

I can't have a SlideKey on this development. You know why? Because a Slide can be repeated in some presentations. That's why some values are in negative. They don't exist, but they are appearing by the SlideKey join.

I think the development must be like this, ordering by hour:

Slide 1

SlideEnter