topic Re: Split table by Time range with INTERVALMATCH? in QlikView

Split table by Time range with INTERVALMATCH?

Mon, 21 Oct 2013 09:21:50 GMT

Dear all,

I have an Oracle SQL system and the table looks very simple

STARTTMS ENDTMS ITEM_no

06/09/13 03:50 06/09/13 05:50 2

08/09/13 14:30 10/09/13 02:00 8

I would like to convert this table into:

DATE ITEM_no Duration

06/09/13 2 2:00

08/09/13 8 9:30

09/09/13 8 24:00

10/09/13 8 02:00

Data was imported via SQL in Edit script:

ODBC CONNECT TO [ASBASE.nlgo.prod;DBQ=ASBASE.nlgo.prod] (XUserId is NWFTdJFITaIA, XPassword is MacWaJFITKUA);

Storing:
LOAD date#(Start, 'DD/MM/YY hh:mm:ss.f'), date#(End, 'DD/MM/YY hh:mm:ss.f'), [Duration];
SELECT STARTTMS ,ENDTMS, ENDTMS-STARTTMS as "Duration"
FROM TRACK."CB_STORING";

How can I proceed to get the desired table, where time
range is split over days including Duration (in hours)?

With INTERVALMATCH?

Could you please advice?

Best regards,

Re: Split table by Time range with INTERVALMATCH?

Gysbert_Wassenaar — Mon, 21 Oct 2013 10:34:40 GMT

See attached qvw

Re: Split table by Time range with INTERVALMATCH?

Mon, 21 Oct 2013 12:22:26 GMT

Thank you for your answer, it works!

Just another question:

Do we have a reference that will give me the required insight about the script language from QV?

Terms like

interval(rangemin(Eind, floor(Start) + IterNo() ) - rangemax(Start,floor(Start) + IterNo() -1),'hh:mm')

is new for me and it may be a good idea to learn more about all ins/outs of this programming script.

thank you again,

Cornelis

Re: Split table by Time range with INTERVALMATCH?

Gysbert_Wassenaar — Mon, 21 Oct 2013 13:25:53 GMT

The help file contains descriptions of what the functions do. But there's no reference that tells you when to use which function. That's something you'll have to pick up for example from discussions on this site and blog posts here and elsewhere. Books like Qlikview 11 for Developers and Qlikview for Developers Cookbook are good sources too.

Re: Split table by Time range with INTERVALMATCH?

Fri, 25 Oct 2013 07:48:09 GMT

Excellent, thanks!