topic Re: HELP! SOS! BIG trouble in QlikView

HELP! SOS! BIG trouble

Wed, 15 Jan 2014 09:10:59 GMT

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

Re: HELP! SOS! BIG trouble

Wed, 15 Jan 2014 09:13:10 GMT

try to use previous function

Re: HELP! SOS! BIG trouble

Wed, 15 Jan 2014 09:15:28 GMT

could you elaborate more

Re: HELP! SOS! BIG trouble

danieloberbilli — Wed, 15 Jan 2014 10:06:28 GMT

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.

Re: HELP! SOS! BIG trouble

rajeshvaswani77 — Wed, 15 Jan 2014 10:20:32 GMT

You could use the above function.

thanks,

Rajesh Vaswani

Re: HELP! SOS! BIG trouble

Wed, 15 Jan 2014 10:23:33 GMT

is that possible you give me an example

Re: HELP! SOS! BIG trouble

Wed, 15 Jan 2014 10:27:45 GMT

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

Re: HELP! SOS! BIG trouble

Luis_Cortizo — Wed, 15 Jan 2014 11:03:30 GMT

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.

Re: HELP! SOS! BIG trouble

sudeepkm — Wed, 15 Jan 2014 11:18:11 GMT

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.