Duplicate values

[tarnee19]

Hi,

I am trying to get values that appear more than once from "days", for example i have:

<string> <string> <date field> <time field>
Days InOut Date Time
----- ------- ------- -------
Sun I 1/17/2005 1am
Sun O 1/17/2005 1pm
Sun I 1/17/2005 2pm
Sun O 1/17/2005 3pm
Mon I 1/18/2005 1am
Mon O 1/18/2005 1pm

i want to have:

Days In Out
---- -- ---
Sun 1am 1pm
2pm 3pm
Mon 1am 1pm

I know how to display in such format using maximum/minimum function, but now I need to know how I can make sure I display times "I/O" that appear more than twice for each day?


Thanks,
Tanzina

 

[MrBillSC]

Can you show a sample of desired output?
MrBill

 

[synapsevampire]

mrbill: You'll need an entirely differnet approach than the grouping I had originally suggested, as tarnee has changed the requiements again.

What you probably want is the rows sorted by the time and then the i/o field, and then display each set of 2 rows alongside each other.

Not too tricky

-k

 

[synapsevampire]

mrbill: You'll need an entirely differnet approach than the grouping I had originally suggested, as tarnee has changed the requiements again.

What you probably want is the rows sorted by the time and then the i/o field, and then display each set of 2 rows alongside each other.

Not too tricky

-k

 

[tarnee19]

I will try the sorting tomorrow, thanks.

I haven't changed the requirements, i just found out that i have to handle such when i displayed the datas.

 

[tarnee19]

I had my fields grouped in Days, so when I use sort using Record sort expert, it does not make any difference to my fields, it still shows the minimum and the maximum for each day only and not all the minimums and the maximums for each day.

I have these fields in details:

Days InOut Date Time
----- ------- ------- -------
Sun I 1/17/2005 1am
Sun O 1/17/2005 1pm
Sun I 1/17/2005 2pm
Sun O 1/17/2005 3pm
Mon I 1/18/2005 1am
Mon O 1/18/2005 1pm


My desired output is:

Days Date In Out
---- ---- --- ---
Sun 1/17/2005 1am 1pm
2pm 3pm
Mon 1/18/2005 1am 1pm


But now I get is this in grouped "Days":

Days Date In Out
---- ---- --- ---
Sun 1/17/2005 1am 3pm
Mon 1/18/2005 1am 1pm


To get In and Out, I used: in group "Days":
<@In>
minimum({@Time},{Days})

<@Out>
maximum({@Time},{Days})

Could you please help, thanks.

Tanzina

 

[lbass]

Try creating a manual crosstab. Group on {table.date} and then add two formulas to the detail section:

//{@In}:
if {table.inout} = "I" then {table.time}

//{@Out}:
if {table.inout} = "O" then {table.time}

Right click on each formula and insert summaries (NthSmallest} multiple times, incrementing the N value by 1 each time, up to the maximum number of start times per day. Place each summary in a separate group footer section. Go to the section expert and format each group footer section to "Suppress blank section". Drag the group header fields into the group footer and then suppress the detail section.

-LB

 

[tarnee19]

How can i calclate the maximum number of start times, please let me know.

Tanzina

 

[lbass]

Since your time field always seems to be hourly, it looks like the absolute maximum would be 24, but to test your actual data, you could insert a summary (count) on {table.time} at the day group level. Then go to topN/group sort and do a topN on "count of {table.time}" descending. The highest count would appear first. You would then divide that by two (if we assume the out time is always on the same day). However, if there could be a start time of any hour of the day, then you should assume 24.

-LB

 

[tarnee19]

How would I divide the count of {@time} field, I dont see any option, pls let me know.

And after you know, the number of "in" times and "out" times, how would I know which ones are "in" times and which are "out" times?


Thanks,
Tanzina

 

[lbass]

You only need to do this to know how many summaries to insert. You don't need to calculate this in the report--this is just to have a guideline. I also assumed that there was an out time for every in time. You are just estimating here and then using the result to determine how many times to insert the NthSmallest (and increment the N) on {@in} and {@out} and how many group footer sections you will need to insert.

As I said before, the maximum count would be 48 per day, or 24 summaries per formula and 24 group footer sections. Your sample display suggests that the number will probably be much smaller than that, and you just need to make sure you have a few more summaries than you will ever actually have in reality--to ensure that you are displaying all results.

-LB