Space being trimmed at end of Column when being concatenated

[AnthonyMJ]

I have 2 database columns, say Suppliers and Product, which I want to concatenate and use as a group. One supplier has the same name but one has a space at the end. When I try to concatenate this supplier with the same product, Crystal Reports seem to treat this record as the same as one without the space. So instead of getting 2 groups, I end up with only 1 group. I wonder if this is a CR bug. Is there any way to get around this ?

I understand that this is somewhat a data quality issue. However, I would like to find a way to handle this error if such a record in fact is correct.

Thanks.

XIR2 on Unix Solaris
Informatica 7.1.3
CRXIR2, Oracle 9i/10g

 

[dgillz]

I assume you are using a formula to concatenate them. Please post your formula.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[AnthonyMJ]

Formula is

ConcatKey = {Query1.Supplier}&{Query1.Product}

Below is an example :-

Supplier Product  ConcatKey  Expected Result
--------+-------+----------+------------------
ABCD      1111    ABCD1111     ABCD1111
ABCD      1111    ABCD1111     ABCD 1111  <-- Supplier has a space at the end

XIR2 on Unix Solaris
Informatica 7.1.3
CRXIR2, Oracle 9i/10g

 

[lbass]

Since you are not trimming the supplier field in the formula, it looks like the trimming of the space is occurring before CR accesses the query. You can verify this by using a formula in CR:

len({table.supplier})

If the result is the same for both, then the issue is not in CR.

-LB

 

[AnthonyMJ]

The length of both suppliers were same. Although I'm still not convinced that the issue is not in CR. I did a distinct select on the same supplier and I got 2 records -- one that has a space and one that doesn't. That means that CR was able to recognize the distinction between the 2 records but somehow ignores the space somewhere. I really can't tell at which point the space is being dropped.

XIR2 on Unix Solaris
Informatica 7.1.3
CRXIR2, Oracle 9i/10g

 

[lbass]

Are you concatenating in the formula area or the SQL expression area?

-LB

 

[AnthonyMJ]

The concatenation is done in the Formula. If it is done in the SQL expression, everything is okay. But still want to see if there is any fix using a formula.

XIR2 on Unix Solaris
Informatica 7.1.3
CRXIR2, Oracle 9i/10g

 

[lbass]

Actually, my point was going to be that I think spaces are sometimes ignored in SQL expressions, NOT in formulas.

-LB

 

[dgillz]

Your formula as posted is incorrect:

ConcatKey = {Query1.Supplier}&{Query1.Product}

This formula will return true or false and ConcatKey either will, or will not, equal the expression. If you are trying to assign a value to ConcatKey, you would normally do this as follows:

ConcatKey[red]:[/red]= {Query1.Supplier}&{Query1.Product}

Note the red colon above. So at this point I am not sure what you are doing. Please cut and paste the entire formula directly from the crystal formula editor window to this thread.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[AnthonyMJ]

The ConcatKey is the name of the Formula. While the formula expression is

{Query1.Supplier}&{Query1.Product}




XIR2 on Unix Solaris
Informatica 7.1.3
CRXIR2, Oracle 9i/10g

 

[scottostanek]

But to get rid of any space discrepencies you could change

{Query1.Supplier}&{Query1.Product}

to

Trim({Query1.Supplier})&Trim({Query1.Product})

I use something like this to build a single sort group formula, but use a paramater to swap them round occasionally

@sortgrp
if {?Sortparam} ="Supplier" then
Trim({Query1.Supplier})&Trim({Query1.Product})
else
Trim({Query1.Product})&Trim({Query1.Supplier})

then group on @sortgrp

Scotto the Unwise

 

[dgillz]

Scott,

The problem is that it is ALREADY getting rid of the spaces and he does not want that.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?