Group by partial data 1

[novicius]

Hi all,

Say, I've done a few searches here at T-T.com and haven't found what I'm looking for. Real simple problem, maybe I'm just looking too hard:

I have a field of data that is organized into a 1234-5678 format. I would like to group only by the '1234' prefix and ignore the suffixes. How might I do that? Function, formula, and if so can you post it?

Thanks!!

- novicius -

 

[synapsevampire]

Create a formula and use:

if instr({table.field},"-") > 1 then
left({table.field},instr({table.field},"-")-1)
else
{table.field}

This will return everything BEFORE the dash, so group on this formula.

It also tests for the dash and if it doesn't find it past the first character, it will use the whole field.

-k

 

[sbreitenfeldt]

This is in response to the answer that you gave Novicius on 10/7. I was a student in the class that asked the question. Being very new to Cyrstal, could you explain how to group on the formula you gave. I have tried a number of things but can't seem to make it work.

Thanks

 

[kskid]

From the toolbar click on Insert -> Group. On the top of the pop-up click on the drop down and select the formula.

That's it.

 

[synapsevampire]

The formula must always return a value, if not it won't be an option to group on.

Otherwise Witchita handled that perfectly.

-k

 

[novicius]

Thanks for the help, Synapse & Witchita!

- novicius -