i need the datas together using condition

[tarnee19]

Hi,

I am trying to display datas from ColumnTimeIn(I) ColumnTimeOut(O) of "ColumnTime" using
conditions from another column "ColumnStatus" related to "ColumnTime".

I have:

ColumnTime ColumnStatus
---------- ------------
1 am I
1 pm O
2 am I
2 pm O
3 am I
3 pm O

I want to have:

ColumnTimeIn(I) ColumnTimeOut(O)
----------- -----------
1 am 1 pm
2 am 2 pm
3 am 3 pm

But I get

ColumnTimeIn(I) ColumnTimeOut(O)
----------- -----------
1 am <blank>
<blank> 1 pm
2 am <blank>
<blank> 2 pm
3 am <blank>
<blank> 3 pm

<blank> are the empty cells that I see when I use "if" in ColumnStatus to find
ColumnTimeIn(I) and ColumnTimeOut(O).

How can I get rid of these empty cells and get the format of output I desire?

This is what I try to do but fail to get rid of empty cells:

<formula>
if {Table.ColumnStatus} = "I" then
{@ColumnTime}

Please help,
thanks.

Tanzina

 

[synapsevampire]

Is this the format of these fields?

A cheater means would be to group by:

val(left({table.timestringfield}),instr({table.timestringfield}," ")-1))

Now in the group header or group footer display using 2 different formulas:

minimum({table.timestringfield},{@MyGroupFormula})

and alongside use:

maximum({table.timestringfield},{@MyGroupFormula})

If it is a datetime field, or a real time field then this would change slightly, but since you've supplied nothing technical about the data I could only guess.

This also assumes that they're in I followed by O order, if not, sort on those fields as well.

-k

 

[tarnee19]

Well, ColumnTime is a real time field, and ColumnStatus is a string type field, i want both the times in real time as well. I will try your suggestion now, thanks.

Tanzina

 

[synapsevampire]

My suggestion should cheat it well enough, just use your time field for display purposes, not the formula.

If it's a time field you'll convert the time as in:

if hour({table.time})> 12 then
hour({table.time})-12
else
hour({table.time})

Now they'll be similarly grouped, and the minimum/maximum will handle the I vs. O as 1 am will be 01:00 and 1 pm will be 13:00.

-k

 

[tarnee19]

I dont think i quite understand -

I have my time field (@time) displayed in hh:mm:ss and takes care of AM and PM, below is the formula:-

<(@Time)>

stringVar str3 := CStr(TimeSerial(hours, mins,secs ), "hh:mm:ss");
if (hours > 12 and hours < 24) then
stringVar str3 + " P.M." else
str3 + "A.M.";
CTime(str3);


My formula is:

<@MyFormula>
if {Table.ColumnStatus} = "I" then
{@Time}

But when i do,
minimum({@Time},{@MyFormula})

it tells me,
"There must be a group that matches this field"

Did I get everything right you said, or am i missing sth? Do i have to create any groups or sth?

Thanks,
Tanzina

 

[synapsevampire]

As I'd feared, your formula is NOT a time, it is a string field resulting from a time field.

If I state that you need a time field, don't substitute a string field and expect it to work.

Use real examples for data, not pseudo examples, and don't call a string a time because you interpret it as a time field, it is still a string type field.

And your solution using the columnstat field has nothing to do with what I suggested.

Try again and use your time field from the database (not your formula converting it to a string) as a group formula:

if hour({table.time})> 12 then
hour({table.time})-12
else
hour({table.time})

For display purposes you can use the minimum/maximum of this field as well in the group header or group footer.

It will work fine.

-k

 

[lbass]

What is it that ties the two time values for I and O together? SV is assuming that it is the hour value (and he might be right), but if there is some other field that links each pair of I and O, then if you group on that field, you can use your original conditional formulas:

if {table.status} = "I" then {@time}//etc.

And then for each formula, insert a maximum (since the only other result will be "").

-LB

 

[tarnee19]

I dont like the way you talk to people k (sv), you might wanna say things nicely next time.

Thanks everyone else, i will try to figure it out.

Tanzina

 

[synapsevampire]

My apologies, I get frustrated when people won't define their requirements, or change them as they go along.

-k