Splitting the data - On the way to granularity

[Anonymous]

Hello community,

I have a really tricky problem:

One Table has State-Information (Field lState)

This state has a start time and a end time.

lState	tStart	tEnd	Duration
1223454	20121126 11:04:33	20121126 11:10:40	6 min
1223455	20121126 06:00:00	20121127 13:00:00	1 d 7 h

I would like to achive a more detailed granularity:

I have three time zones per day:

TZ1: 06:00:00 - 14:00:00

TZ2: 14:00:00 - 22:00:00

TZ3: 22:00:00 - 06:00:00 (on the next day)

The split should lead to the following output:

ID	lState	tStart	tEnd	Duration	Day	TimeZone
1	1223454	20121126 11:04:33	20121126 11:10:40	6 min	20121126	TZ1
2	1223455	20121126 06:00:00	20121126 14:00:00	8 h	20121126	TZ1
3	1223455	20121126 14:00:00	20121126 22:00:00	8 h	20121126	TZ2
4	1223455	20121126 22:00:00	20121127 06:00:00	8 h	20121126	TZ3
5	1223455	20121127 06:00:00	20121127 13:00:00	7 h	20121127	TZ1

For sure I'll need some for next loops , but as I'm quite new to QlikView this is a too big fish for me.

Maybe somebody is interested in solving this nice problem or has already solved a similar problem.

Any help is welcome

Cheers

//chesterluck

[Gysbert_Wassenaar]

See attached qvw. Somebody can probably do this more efficiently, but this kludge seems to work at least.

[Anonymous]

Hey it looks already not bad, but still there is is a logical error.

I loaded new record to test the split:

lState,tStart,tEnd,Duration

1223456,20121128 02:00:33, 20121130 01:02:02, 1 d 4 h

Here is the output:

The target-output should look like this:

Original dataset: 1223456, 20121128 02:00:33, 20121130 01:02:02

New_Id	lState	tBegin	tEnd	Duration (Calculated)	TimeZone	Date
1223456_1	1223456	20121128 02:00:33	20121128 05:59:59	tEnd-tBegin	TZ3	20121128
1223456_2	1223456	20121128 06:00:00	20121128 13:59:59	tEnd-tBegin	TZ1	20121128
1223456_3	1223456	20121128 14:00:00	20121128 21:59:59	tEnd-tBegin	TZ2	20121128
1223456_4	1223456	20121128 22:00:00	20121129 05:59:59	tEnd-tBegin	TZ3	20121129
1223456_5	1223456	20121129 06:00:00	20121129 13:59:59	tEnd-tBegin	TZ1	20121129
1223456_6	1223456	20121129 14:00:00	20121129 21:59:59	tEnd-tBegin	TZ3	20121129
1223456_7	1223456	20121129 22:00:00	20121130 01:02:02	tEnd-tBegin	TZ3	20121130

I ll try to change the code, but it would be great if you also would have a look

Thanks a lot

//chesterluck

[Anonymous]

Hi all,

I elaborated a bit and wrote soe code, maybe it is helpful to answer the question. As I said, Im quite new to QlikView

and please you to exuse my mistakes. Here the code:

ps Thanks a lot

Inline_Load:

load
lState,
Timestamp#(tStart,'YYYYMMDD hh:mm:ss') as tStart,
Timestamp#(tEnd,'YYYYMMDD hh:mm:ss') as tEnd,
hour(Timestamp#(tStart,'YYYYMMDD hh:mm:ss')) as hStart,
hour(Timestamp#(tEnd,'YYYYMMDD hh:mm:ss')) as hEnd, 
tEnd - tStart as Duration

Inline [
lState,tStart,tEnd
1223454,20121126 11:04:33, 20121128 13:10:40
];

//Variables
Set tSplitEnd = 'YYYYMMDD hh:mm:ss';
Set tSplitStart = 'YYYYMMDD hh:mm:ss';

If ((hStart >= 22 and hEnd < 6 and Duration <= 😎 OR (hStart >= 6 and hEnd < 14 and Duration <= 😎 OR (hStart >= 14 and hEnd < 22 and Duration <= 8)) Then
//MsgBox("Do Nothing")
ELSE

Let tSplitStart = tStart;

If hStart > 22 Then
Let tSplitEnd = Timestamp#(Date#(tBegin, 'YYYYMMDD') + 1 + MakeTime(5,59,59), 'YYYYMMDD hh:mm:ss');

ELSEIF hStart < 6 Then
Let tSplitEnd = Timestamp#(Date#(tBegin, 'YYYYMMDD') + MakeTime(5,59,59), 'YYYYMMDD hh:mm:ss');

ELSEIF hStart < 14 Then
Let tSplitEnd = Timestamp#(Date#(tBegin, 'YYYYMMDD') + MakeTime(13,59,59), 'YYYYMMDD hh:mm:ss');

ELSE
Let tSplitEnd = Timestamp#(Date#(tBegin, 'YYYYMMDD') + MakeTime(21,59,59), 'YYYYMMDD hh:mm:ss');

End If



For i = 1 to Duration/8 + 2 //2 as appx add. quant
  If tSplitBegin < tEnd Then
 
   tStart = tSplitStart;
  
   If tSplitEnd <= tEnd Then
   Let tEnd = tSplitEnd;
   Else
   Let tEnd = tEnd;
   End If
  
   tSplitBegin = Timestamp#(Date#(tSplitBegin, 'YYYYMMDD') + MakeTime(8,0,0), 'YYYYMMDD hh:mm:ss');
      tSplitEnd = Timestamp#(Date#(tSplitEnd, 'YYYYMMDD') + MakeTime(8,0,0), 'YYYYMMDD hh:mm:ss');
   
        ELSE
     End If
     
    Next
//Should be used somewhere in the code
//concatenate (Inline_Load) load

[Gysbert_Wassenaar]

Ok, so you want Day to be the day part of tEnd and not of tStart. That can't be determined from your first example. But it's easy enough to do. Copy the calculation for tEnd, change the date format to 'YYYYMMDD' (leaving out th hh:mm:ss) and name it as Day.

[Anonymous]

Im a bit unsure whether you understood me right.

I also figured it out with the end date , so thats fine.

But in your solution, as you can see (red color), its counting 8 hrs to the start timestamp.

As you can see below, there is exactly one data set with the original tBegin and exactly one with the original tEnd (Red color). Depending on the TimeZone (defined in the original question) all the added tBegin can have three possible characteristics (06:00:00;14:00:00;22:00:00); all the added tEnd (05:59:59;13:59:59;21:59:59) :

[swuehl · Accepted solution]

Try like this:

INPUT:

load

lState,

Timestamp#(tStart,'YYYYMMDD hh:mm:ss') as tStart,

Timestamp#(tEnd,'YYYYMMDD hh:mm:ss') as tEnd

Inline [

lState,tStart,tEnd,Duration

1223454,20121126 11:04:33,    20121126 11:10:40,6 min

1223455,20121126 06:00:00, 20121127 13:00:00,1 d 7 h

1223456,20121128 02:00:33, 20121130 01:02:02, 1 d 4 h

1223457,20121128 19:00:33, 20121130 01:02:02, 1 d 4 h

];

RESULT:

LOAD *, interval(End-Start) as Duration;

LOAD *, if(tStart > TZStart, tStart, TZStart) as Start, if(tEnd < TZEnd, tEnd, TZEnd) as End ;

LOAD iterno() as ShiftID, lState, tStart, tEnd,

timestamp(floor(tStart,1/3,1/4)+(iterno()-1)*1/3) as TZStart,

timestamp(floor(tStart,1/3,1/4)+iterno()*1/3-interval#(1,'fff') ) as TZEnd,

mod(round((floor(tStart,1/3,1/4)+(iterno()-1)*1/3)*12),3)+1 as TZ

Resident INPUT

while floor(tStart,1/3,1/4) + (iterno()-1)/3 <= floor(tEnd,1/3,1/4);

drop table INPUT;

[Anonymous]

Guys you're the best. Thanks a lot!!! In the meantime I'll try to understand the code

//chesterluck

[Anonymous]

Hi swuehl,

the splitt works fine ..almost: I figured out one example where it doesnt work :

I have no clue where the mistake in the code is.. Normally it should produce 2 raws - 2nd raw ShiftID 2 from 14:00:00 until 21:55:18...Thank you in advance

[swuehl]

I can't open the attached image. Could you post the problematic input data:

LOAD * Inline [

lState,tStart,tEnd

....

]