Generate rows for min and sec

[poonam_kulwal]

Hi All,

I need to generate rows for missing minutes and Seconds between two datetime field for particular JobId.

Ex:        J1 ran for 15sec

J2 ran for 4min

J3 ran for 8min25sec

I am looking for generating rows between StartDate and EndDate field for respective min and sec with Status as ‘Active’ ,‘Finished’.

I know how to generate rows between two dates. But here I have both dates same just difference in Min and Sec of JobID.

Following script will generate missing date but not min and sec.

load *,

Date(StartDate + iterno()-1, 'MM-DD-YYYY h:mm:ss') as month

Resident JobTable

While StartDate + IterNo()-1 <= EndDate;

Appreciate any help on this.

Regards,

Poonam

[sunny_talwar · Accepted solution]

More like this

LOAD  *,

     TimeStamp(StartDate + ((IterNo() - 1)/86400), 'MM-DD-YYYY h:mm:ss') as month

Resident JobTable

While StartDate + ((IterNo() - 1)/86400) <= EndDate;

[poonam_kulwal]

Hi Sunny,

Thanks a lot for such a quick reply. Its working.

Regards,

Poonam

[sunny_talwar]

Awesome, I am glad it worked. Please close this thread if you got what you wanted

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

[poonam_kulwal]

Hi Sunny,

Need to ask one more question.

If I want to exclude sec then what changes I need to do in your script. only for hh:mm.

Thanks,

Poonam

[sunny_talwar]

May be like this

LOAD  *,

     TimeStamp(Floor(StartDate, 1/1440) + ((IterNo() - 1)/1440), 'MM-DD-YYYY h:mm') as month

Resident JobTable

While Floor(StartDate, 1/1440) + ((IterNo() - 1)/1440) <= Floor(EndDate, 1/1440);

[poonam_kulwal]

Hi Sunny,

I am getting correct result for J1 and J3 but not for J2.

For J2 last record is not matching with EndDate field.

What can be the reason?

see images

Regards,

Poonam

[poonam_kulwal]

Hi Sunny,

For some reason I am not getting last record.

Here I need one more record

J2      01-02-2017 11:30:10 AM     01-02-2017 11:34:10 AM      01-02-2017 11:34

Thanks & Regards,

Poonam

[sunny_talwar]

Try this:

LOAD  *,

     TimeStamp(Floor(StartDate, 1/1440) + ((IterNo() - 1)/1440), 'MM-DD-YYYY h:mm') as month

Resident JobTable

While Floor(StartDate, 1/1440) + ((IterNo() - 1)/1440) <= (Floor(EndDate, 1/1440) + 1/1440);

[sunny_talwar]

Are you doing to minutes or seconds now?