HELP! SOS! BIG trouble

[#1]

i have a list of table data like this format just for an example,

Member_id	TeacherName	StartDate	StartTime	EndTime
27142753	A	2013-11	11/1/2013 1:00	11/1/2013 4:00
26966290	B	2013-11	11/1/2013 2:00	11/1/2013 12:30
27544044	C	2013-11	11/1/2013 2:00	11/1/2013 12:30

i want to subtract each slot hour between field startime and endtime into a new data columnn.

to end up it into like this ,i do not want to show the end time hour here

Member_id	Name	StartDate	StartTime	EndTime	Slot
27142753	A	2013-11	11/1/2013 1:00	11/1/2013 4:00	1:00
27142753	A	2013-11	11/1/2013 1:00	11/1/2013 4:00	2:00
27142753	A	2013-11	11/1/2013 1:00	11/1/2013 4:00	3:00
26966290	B	2013-11	11/1/2013 2:00	11/1/2013 5:00	2:00
26966290	B	2013-11	11/1/2013 2:00	11/1/2013 5:00	3:00
26966290	B	2013-11	11/1/2013 2:00	11/1/2013 5:00	4:00
27544044	C	2013-11	11/1/2013 6:00	11/1/2013 9:00	6:00
27544044	C	2013-11	11/1/2013 6:00	11/1/2013 9:00	7:00
27544044	C	2013-11	11/1/2013 6:00	11/1/2013 9:00	8:00

i do not know how to do it in qlikview or SQL scrip

[#2]

try to use previous function

[#3]

could you elaborate more

[danieloberbilli]

I dont know exactly how it works but just as an idea: you need a loop in your load script in order to duplicate the IDs and with (EndTime -StartTime)-times of iterations where Slot is StartTime + Number of iteration.

[rajeshvaswani77]

You could use the above function.

thanks,

Rajesh Vaswani

[#6]

is that possible you give me an example

[#7]

Hi Max,

Perhaps use the IterNo() function that can iterate over each line.

See an answer in :

How to tranform table/generate data

The problem (or question) seems very similar.

Fabrice

[Luis_Cortizo]

Hello, Max

  The desired output for the data you have posted, would be something like this, right?

Member_id	TeacherName	StartDate	StartTime	EndTime	Slot
27142753	A	2013-11	11/1/2013 1:00	11/1/2013 4:00	1
27142753	A	2013-11	11/1/2013 1:00	11/1/2013 4:00	2
27142753	A	2013-11	11/1/2013 1:00	11/1/2013 4:00	3
26966290	B	2013-11	11/1/2013 2:00	11/1/2013 12:30	2
26966290	B	2013-11	11/1/2013 2:00	11/1/2013 12:30	3
26966290	B	2013-11	11/1/2013 2:00	11/1/2013 12:30	4
26966290	B	2013-11	11/1/2013 2:00	11/1/2013 12:30	5
26966290	B	2013-11	11/1/2013 2:00	11/1/2013 12:30	6
26966290	B	2013-11	11/1/2013 2:00	11/1/2013 12:30	7
26966290	B	2013-11	11/1/2013 2:00	11/1/2013 12:30	8
26966290	B	2013-11	11/1/2013 2:00	11/1/2013 12:30	9
26966290	B	2013-11	11/1/2013 2:00	11/1/2013 12:30	10
26966290	B	2013-11	11/1/2013 2:00	11/1/2013 12:30	11
27544044	C	2013-11	11/1/2013 2:00	11/1/2013 12:30	2
27544044	C	2013-11	11/1/2013 2:00	11/1/2013 12:30	3
27544044	C	2013-11	11/1/2013 2:00	11/1/2013 12:30	4
27544044	C	2013-11	11/1/2013 2:00	11/1/2013 12:30	5
27544044	C	2013-11	11/1/2013 2:00	11/1/2013 12:30	6
27544044	C	2013-11	11/1/2013 2:00	11/1/2013 12:30	7
27544044	C	2013-11	11/1/2013 2:00	11/1/2013 12:30	8
27544044	C	2013-11	11/1/2013 2:00	11/1/2013 12:30	9
27544044	C	2013-11	11/1/2013 2:00	11/1/2013 12:30	10
27544044	C	2013-11	11/1/2013 2:00	11/1/2013 12:30	11

Wihout taking into account that some EndTimes doesn't finish in 60 minutes (hence the 11 slot even when the EndTime is 12:30).

For this kind of solution, instead of overloading the layout level with complex calculation is quite straightforward to perform that kind of calculation on the Script layer, I have loaded your sample data on an Inline Table and performed the slot calculation on two Precendet Loads:

Data:

Load

  Member_id,

  TeacherName,

  StartDate,

  StartTime,

  EndTime,

  Hour(StartTime) + iterno()-1 as Slot

WHILE  Hour(StartTime) + iterno()-1 < Hour(EndTime);

LOAD

  num#(Member_id) as Member_id,

  TeacherName,

  StartDate,

  Timestamp#(StartTime, 'DD/M/YYYY h:mm') as StartTime,

  Timestamp#(EndTime, 'DD/M/YYYY h:mm') as EndTime;

LOAD * INLINE [

    Member_id, TeacherName, StartDate, StartTime, EndTime

    27142753, A,  2013-11, 11/1/2013 1:00, 11/1/2013 4:00

    26966290, B,  2013-11, 11/1/2013 2:00, 11/1/2013 12:30

    27544044, C,  2013-11, 11/1/2013 2:00, 11/1/2013 12:30

];

Please find attached a QlikView document example.

[sudeepkm]

Have you tried IntervalMatch in your load script?

You can define the slot hours in a separate table using autogenerate for 24 hours. Each row having start time and end time and the slot #.

Then the original data table can be loaded and then you can use the intervalmatch load. Please refer to the reference manual or help file for more details.