Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Splitting the data - On the way to granularity

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.

lStatetStarttEndDuration
122345420121126 11:04:3320121126 11:10:406 min
122345520121126 06:00:0020121127 13:00:001 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:

IDlStatetStarttEndDurationDayTimeZone
1122345420121126 11:04:3320121126 11:10:406 min20121126TZ1
2122345520121126 06:00:0020121126 14:00:008 h20121126TZ1
3122345520121126 14:00:0020121126 22:00:008 h20121126TZ2
4122345520121126 22:00:0020121127 06:00:008 h20121126TZ3
5122345520121127 06:00:0020121127 13:00:007 h20121127TZ1

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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;

View solution in original post

15 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

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:

ScreenSplit.jpg

The target-output should look like this:


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

New_IdlStatetBegintEndDuration (Calculated)TimeZoneDate
1223456_1122345620121128 02:00:3320121128 05:59:59tEnd-tBeginTZ320121128
1223456_2122345620121128 06:00:0020121128 13:59:59tEnd-tBeginTZ120121128
1223456_3122345620121128 14:00:0020121128 21:59:59tEnd-tBeginTZ220121128
1223456_4122345620121128 22:00:0020121129 05:59:59tEnd-tBeginTZ320121129
1223456_5122345620121129 06:00:0020121129 13:59:59tEnd-tBeginTZ120121129
1223456_6122345620121129 14:00:0020121129 21:59:59tEnd-tBeginTZ320121129
1223456_7122345620121129 22:00:0020121130 01:02:02tEnd-tBeginTZ320121130

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

Thanks a lot

//chesterluck

Anonymous
Not applicable
Author

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.


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

ScreenSplit.jpg

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) :

Unbenannt.jpg

swuehl
MVP
MVP

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
Not applicable
Author

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

//chesterluck

Anonymous
Not applicable
Author

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

error.png

swuehl
MVP
MVP

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

LOAD * Inline [

lState,tStart,tEnd

....

]