Split table by Time range with INTERVALMATCH?

[#1]

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,

[Gysbert_Wassenaar · Accepted solution]

See attached qvw

[#3]

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

[Gysbert_Wassenaar]

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.

[#5]

Excellent, thanks!