Calculate data_duration

[martinqlik]

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';

[sunny_talwar]

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;

[martinqlik]

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

Could you check this QVD out?

[sunny_talwar]

May be like this:

FinalTable:

LOAD *,

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

Resident Slide_Enter;

[sunny_talwar · Accepted solution]

Check the attached may be

[martinqlik]

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.

[sunny_talwar]

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;

[martinqlik]

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.

[sunny_talwar]

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,

[martinqlik]

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

11:10

Slide 1

SlideExit

11:12

Slide 2

SlideEnter

11:15

Slide 2

SlideExit

11:16

Slide 1

SlideEnter

11:17

Slide 1

SlideExit

11:20

Slide 1

Duration: 00:02

Slide 2

Duration: 00:01

Slide 3

Duration: 00:03

Could you help me to make a for statement for that?

Thank you