Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
 
					
				
		
 qw_johan
		
			qw_johan
		
		
		
		
		
		
		
		
	
			
		
		
			
					
		Hi,
I have a long list of id numbers, dates and times(start and end time).
My problem is that certain dates have more that one start and end time (see picture below).
What I'd like to get is a list with only the lowerst start time and the highest end
time on the dates where there are several start/end times..the rest should remain like they are.
For examples with the marked dates in the picture below...
20110125 0725 1530
20110201 0710 1535
20110203 0700 1630
20110207 0705 1730
Can this be accomplished in qlikview?
Thanks

 
					
				
		
 swuehl
		
			swuehl
		
		
		
		
		
		
		
		
	
			
		
		
			
					
		Seems like you don't know how to handle the two dates (start date and end date).
You should group by only id, one date and no time. I assumed that sum(break_min) is something meaningful, but don't know.
Try maybe like this:
LOAD id,
date_f,
min(time_f) as time_f,
max(date_t) as date_t,
max(time_t) as time_t,
sum(break_min) as break_min
FROM
TB03_20111103_205945.xls(biff, embedded labels, table is Sheet1$)
group by id, date_f;
Regards,
Stefan
 
					
				
		
Hello,
Try to use min and max to solve this.
E.g.
Load
Date,
min(Start) as Start,
max(End) as End
From xxx
group by Date;
br
Martin
 
					
				
		
 qw_johan
		
			qw_johan
		
		
		
		
		
		
		
		
	
			
		
		
			
					
		Hi,
I have tried that but I get a script error saying "Invalid expression".
Thanks
 
					
				
		
Hi John,
Try this
DateTime:
load * Inline
[Date,Start,End
20110125, 0725, 1530
20110125, 0720, 1520
20110201, 0710, 1535
20110201, 0715, 1525
20110203, 0750, 1650
20110203, 0700, 1630
20110207, 0708, 1740
20110207, 0705, 1730
];
Load
Date as Date1,
Min(Start) as MinStart,
Max(End) as MaxEnd
Resident DateTime
group by Date;
Reply me if it works for you or not!!
Hope it will helps you
Regards..
 
					
				
		
 qw_johan
		
			qw_johan
		
		
		
		
		
		
		
		
	
			
		
		
			
					
		No, it doesn't work. Doesn't make a difference.
This is my code...
WorkTimes:
LOAD id,
date_f,
min(time_f) as time_f,
date_t,
max(time_t) as time_t,
break_min
FROM
TB03_20111103_205945.xls(biff, embedded labels, table is Sheet1$)
group by id, date_f, time_f, date_t, time_t, break_min;
I upload a qvw and excel file for example.
 
					
				
		
try using the attached logic in your script ..think it should give you the results you wish..
 
					
				
		
 swuehl
		
			swuehl
		
		
		
		
		
		
		
		
	
			
		
		
			
					
		Seems like you don't know how to handle the two dates (start date and end date).
You should group by only id, one date and no time. I assumed that sum(break_min) is something meaningful, but don't know.
Try maybe like this:
LOAD id,
date_f,
min(time_f) as time_f,
max(date_t) as date_t,
max(time_t) as time_t,
sum(break_min) as break_min
FROM
TB03_20111103_205945.xls(biff, embedded labels, table is Sheet1$)
group by id, date_f;
Regards,
Stefan
 
					
				
		
 qw_johan
		
			qw_johan
		
		
		
		
		
		
		
		
	
			
		
		
			
					
		Thanks Stefan. This was exactly what I was looking for.
