topic Re: need help in QlikView

need help

mshailaja — Wed, 25 Nov 2020 16:16:04 GMT

HI ,

i have opendate of the incident. i want to see the time differnce of that incident

The calculation which user has given is currrentdate-opendate<60 minutes. How to implement in script level( i need the output as in minutes)

i have tried as interval(Timestamp(now)-Tiestamp(Opendate))<60. i am not getting correct output.please help me

Re: need help

Chanty4u — Wed, 15 Feb 2017 09:45:00 GMT

try like below

=interval( (TIMESTAMP2 - TIMESTAMP1)<60, 'hh:mm')

Re: need help

Chanty4u — Wed, 15 Feb 2017 09:48:08 GMT

or may be

Timestamp((Timestamp#(EndDateTime) - Timestamp#(StartDateTime))<60, 'hh:mm:ss')

Re: need help

kaushiknsolanki — Wed, 15 Feb 2017 09:49:55 GMT

Try

interval(Timestamp(now)-Timestamp(Opendate),'mm')

Regards,

Kaushik Solanki

Re: need help

mshailaja — Wed, 15 Feb 2017 09:55:36 GMT

in the script i have wriiten as

Interval(Timestamp(now())- Timestamp(INOPEN_DATE)<60,'HH;MM') as TimeTaken;

the output is not showing as per above screenshot

Re: need help

Chanty4u — Wed, 15 Feb 2017 10:01:45 GMT

interval(Timestamp(now())- Timestamp(INOPEN_DATE)<60,'hh;mm') as TimeTaken;

interval(Timestamp(now())- Timestamp(INOPEN_DATE),'hh;mm') as TimeTaken;

Re: need help

sdmech81 — Wed, 15 Feb 2017 10:05:20 GMT

Hi plss send some sample data.

When u take the difference its better to use NUm() on dates n then compare..

Sachin

Re: need help

mshailaja — Wed, 15 Feb 2017 10:05:57 GMT

it is not coming chanty

Re: need help

Chanty4u — Wed, 15 Feb 2017 10:07:58 GMT

try this

floor((TimeEnd-TimeStart)*24*60*60)

Re: need help

Chanty4u — Wed, 15 Feb 2017 10:10:24 GMT

May be this

Interval (Timestamp#(TimeEnd,'DD/MM/YYYY hh:mm:ss') - Timestamp#(INOPEN_DATE,'DD/MM/YYYY hh:mm:ss TT'), 'hh:mm:ss TT')

Re: need help

sasikanth — Wed, 15 Feb 2017 10:17:44 GMT

Try this in Script:

LOAD

Opendate,

ceil(Timestamp(Now())- Timestamp(opendate))*24*60 AS Minutes,

From Tabale..

Re: need help

mshailaja — Wed, 15 Feb 2017 10:30:06 GMT

Open date as

1/1/2016 12:00:05 AM

1/1/2016 12:00:25 AM

1/1/2016 12:00:15 AM

1/1/2016 12:00:50 AM

1/1/2016 12:00:35 AM

1/1/2016 12:00:45 AM

1/1/2016 12:00:12 AM

Here i have calculations as (Current date- Opendate)<60 mins

so i have written in script as

Timestamp(Timestamp(now())- (INOPEN_DATE)<60,'HH;MM') as TimeTaken,
Interval(Timestamp#(now(),'DD/MM/YYYY hh:mm:ss') - Timestamp#(INOPEN_DATE,'DD/MM/YYYY hh:mm:ss TT'), 'hh:mm:ss TT') as time

i have written like above but the output is coming wrong

Please help me

Re: need help

MayilVahanan — Wed, 15 Feb 2017 10:35:22 GMT

Please Post some sample data and expected output to fix the issue.

Re: need help

mshailaja — Wed, 15 Feb 2017 10:49:39 GMT

I WANT TO SHOW THE CALCULATION AS (cURRENT DATE- OPENDATE)<60 MINUTES AND THE OUTPUT TO BE SHOWN IN MINUTES

Re: need help

MayilVahanan — Wed, 15 Feb 2017 10:54:18 GMT

Try like this

If(Interval(Timestamp#(now(),'DD/MM/YYYY hh:mm:ss') - Timestamp#(INOPEN_DATE,'DD/MM/YYYY hh:mm:ss TT'), 'mm') < 60,

Interval(Timestamp#(now(),'DD/MM/YYYY hh:mm:ss') - Timestamp#(INOPEN_DATE,'DD/MM/YYYY hh:mm:ss TT'), 'mm')) As TimeDifference

Re: need help

mshailaja — Wed, 15 Feb 2017 12:55:29 GMT

Hi one morething , here the opendate is in GMT timings

andin the calculations i am using Now function.

the server is located in US.

so if i use now function the calculation will go wrong.

So i need to convert the Current time as GMT timings (i cannot use Now function as it is in US server)

Can any one help me how to write the calculation (currentdate(which i have to convert into GMT)- Opendate)<60 mins

Re: need help

sasiparupudi1 — Wed, 15 Feb 2017 14:00:19 GMT

Try this

Load

Timestamp(ConvertToLocalTime(UTC(), 'GMT-05:00'),'DD/MM/YYYY hh:mm:ss') as USNeWYorkTime,

Timestamp(Timestamp#(INOPEN_DATE,'DD/MM/YYYY hh:mm:ss TT'),'DD/MM/YYYY hh:mm:ss') as INOPEN_DATE,

Interval(Timestamp(ConvertToLocalTime(UTC(), 'GMT-05:00'),'DD/MM/YYYY hh:mm:ss')- Timestamp(Timestamp#(INOPEN_DATE,'DD/MM/YYYY hh:mm:ss TT'),'DD/MM/YYYY hh:mm:ss'),'mm') as TimeTaken// Diff in minutes

;

Load * Inline

[

INOPEN_DATE

1/1/2016 12:00:05 AM

1/1/2016 12:00:25 AM

1/1/2016 12:00:15 AM

1/1/2016 12:00:50 AM

1/1/2016 12:00:35 AM

1/1/2016 12:00:45 AM

1/1/2016 12:00:12 AM

];

hth

Sasi

Re: need help

annafuksa1 — Wed, 15 Feb 2017 14:32:10 GMT

try

if(num(Timestamp#([Open date],'D/M/YYYY hh:mm:ss[.fff]'))- num('$(vNow)') <0.00070601851621177 ,1,0) as [60minFlagnum] ,

where vNow= now()

Re: need help

MayilVahanan — Thu, 16 Feb 2017 05:07:56 GMT

If(Interval(Timestamp(ConvertToLocalTime(UTC(), 'GMT-05:00'),'DD/MM/YYYY hh:mm:ss') - Timestamp#(INOPEN_DATE,'DD/MM/YYYY hh:mm:ss TT'), 'mm') < 60,

Interval(Timestamp#(ConvertToLocalTime(UTC(), 'GMT-05:00'),'DD/MM/YYYY hh:mm:ss') - Timestamp#(INOPEN_DATE,'DD/MM/YYYY hh:mm:ss TT'), 'mm')) As TimeDifference