topic Re: PROBLEM WITH DATE, TIME AND INTERVAL OF TIME in QlikView

PROBLEM WITH DATE, TIME AND INTERVAL OF TIME

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

Hi,

1) I must extract information from an xml that is insiede a specific oracle's db table...i've find the correct query that is below:

SELECT r.numero as IDREFERTO

,sr.id || '_' || rownum IDsSTEMI

,nvl(EXTRACTVALUE(value(Modulo),'//child::*[@modelCodeScheme="prova" and @modelCodeValue="1"]/text()'),'campo vuoto') PROVA1_DATA

,nvl(EXTRACTVALUE(value(Modulo),'//child::*[@modelCodeScheme="prova" and @modelCodeValue="1.1"]/text()'),'campo vuoto') PROVA1_ORA

,nvl(EXTRACTVALUE(value(Modulo),'//child::*[@modelCodeScheme="prova" and @modelCodeValue="2"]/text()'),'campo vuoto') PROVA2_DATA

,nvl(EXTRACTVALUE(value(Modulo),'//child::*[@modelCodeScheme="prova" and @modelCodeValue="2.1"]/text()'),'campo vuoto') PROVA2_ORA

FROM TBREF c

join REFER r on c.REFER = r.NUMERO

join SR sr on sr.SI_ID = r.SI_ID

, TABLE(XMLSEQUENCE(EXTRACT(XMLType(REPLACE(sr.xmldata, 'xmlns="http://www.PROVA.it/2008/FMSchema"', '')),'//modulo'))) Modulo

WHERE sr.XMLDATA is not null;

An example of the query's outputs are:

PROVA1_DATA PROVA1_ORA PROVA2_DATA PROVA2_ORA

2018-07-25 03:00:00 2018-07-26 09:40:00

2) NOW THERE IS THE PROBLEM!!!

I MUST CALCULATE THE DIFFERENCE (IN MINUTES) BETWEEN DATA1+ORA1 E DATA2+ORA2

How I can do it? there is a formula that i can use in qlik? or the solution is with a special pl/sql query in the script?

3) at the end i must calculate media, mediana and dev std over all the outpus of the query @ point 1

CAN YOU HELP ME???

PLEASE

Re: PROBLEM WITH DATE, TIME AND INTERVAL OF TIME

qlikviewwizard — Fri, 27 Jul 2018 01:39:32 GMT

Hi,

Can you attach the sample data?

Re: PROBLEM WITH DATE, TIME AND INTERVAL OF TIME

jyothish8807 — Fri, 27 Jul 2018 05:52:03 GMT

Hi Eleonora,

For your 2nd point try lime this:

= interval(Timestamp#( PROVA2_DATA &' '& PROVA2_ORA,'YYYY-MM-DD hh:mm:ss') - Timestamp#(PROVA1_DATA &' '& PROVA1_ORA,'YYYY-MM-DD hh:mm:ss'),'mm')

Br,

Re: PROBLEM WITH DATE, TIME AND INTERVAL OF TIME

gabroteddy — Fri, 27 Jul 2018 06:47:56 GMT

HI,

thanks for the interest.....

ATTENTION: as I wrote in the previous post....I have no problems in the

extraction of data but in presenting them in a different form in the

dashboards of qlik!!!

So...I

1) I MUST CREATE A FIELD THAT IS AN AGGREGATION OF DATA1+ORA1 CALCULATE

THE DIFFERENCE (IN MINUTES) BETWEEN DATA1ORA1 E DATA2ORA2 AND HAS A

FORMAT LIKE 'DD-MM-YYYY hh:mm:ss'

2) I MUST CALCULATE THE DIFFERENCE (IN MINUTES) BETWEEN THE FIELDS CREATED

ABOVE (DATA1ORA1) - (DATA2ORA2)

Below there is the xml data contained in the db....

Re: PROBLEM WITH DATE, TIME AND INTERVAL OF TIME

gabroteddy — Fri, 27 Jul 2018 07:03:32 GMT

the example data can be found in the annex

2018-07-27 3:39 GMT+02:00 arjun rao <qcwebmaster@qlikview.com>:

Re: PROBLEM WITH DATE, TIME AND INTERVAL OF TIME

gabroteddy — Fri, 27 Jul 2018 07:13:08 GMT

Jyothish,

thx...the formula is ok but the result is a negative interval.

Example:

PROVA1_DATA PROVA1_ORA PROVA2_DATA PROVA2_ORA

2018-03-07 11:11:00 2018-03-07

12:02:00

INTERVAL TIME IS

-51

WHY?

2018-07-27 7:52 GMT+02:00 Jyothish KC <qcwebmaster@qlikview.com>:

Re: PROBLEM WITH DATE, TIME AND INTERVAL OF TIME

jyothish8807 — Fri, 27 Jul 2018 07:18:41 GMT

Just change the order as per the business requirement:

= interval(Timestamp#( PROVA1_DATA &' '& PROVA1_ORA,'YYYY-MM-DD hh:mm:ss') - Timestamp#(PROVA2_DATA &' '& PROVA2_ORA,'YYYY-MM-DD hh:mm:ss'),'mm')

Br,

Re: PROBLEM WITH DATE, TIME AND INTERVAL OF TIME

jyothish8807 — Fri, 27 Jul 2018 07:19:38 GMT

If you have "Start Time" and "End Time", Then use

Interval(Endtime-StartTime,'mm')

Br,

Re: PROBLEM WITH DATE, TIME AND INTERVAL OF TIME

gabroteddy — Fri, 27 Jul 2018 08:09:33 GMT

OKOK!!

Thx...the output is correct....now i must resolve the other question.

When i have a lot of data i must take the Media, Mediana and Standard

Deviation of all case.

I attach the dashboard thatr i have in output.

What i want do is calculate the media, mediana and devstd of only the ID

where th interval is correctly calculate (sometime is not valorized)...if

is possible in a separate dashboard.

Actually with the formula = interval(Timestamp#( PROVA1_DATA &' '&

PROVA1_ORA,'YYYY-MM-DD hh:mm:ss') - Timestamp#(PROVA2_DATA &' '&

PROVA2_ORA,'YYYY-MM-DD hh:mm:ss'),'mm') i've calculated the interval in

each row...but i don't know how i can utilize this formula to create a

specific field that i can utilize in other dashboard.

How i can do?

2018-07-27 9:19 GMT+02:00 Jyothish KC <qcwebmaster@qlikview.com>: