Reference 1st digit in column

[MrsGG]

Hello All

I have a report that has the below ID data.

The start of the ID determines which naming convention it is part of so

 Starts with 1 = General

Starts with 2 = Clothing

This goes right up to 9.

Is it better to duplicate column leaving the first digit then refer to another table or is there any other better way?

ID	Name
1	General
11	General
112.000	General
2	Clothing
26	Clothing
264	Clothing
27	Clothing
3	Shoes
33	Shoes
334	Shoes

Thanks in advance

[MrsGG]

Hi Greg

The main table has got 4000ish records, they are from 1 upto 9 and can be:

ID	Name	Cost
1	General	100
11	General	200
110.119	General	200
2	Clothing	100
22	Clothing	100
220.119	Clothing	100

Each of the starting numbers is a new section name, but every number under that section name is the same.

We would like to add the name as its own column in order to pull that through and get a total cost i.e. clothing 300

(we could do that with a another table and relationship yes) but in case of any added ones we would had to revise that?

We also have another 6 of source data sets that that have their own similar but not exact the same numbering but the prefix is always the same so I thought (maybe wrongly) if we calculate off of the 1st digit that covers us for any varying data sets and additions, maybe...

Hope that make sense, crude example below.

Johnny
1	General	100
11	General	200
110.119	General	200
2	Clothing	100
22	Clothing	100
220.119	Clothing	100
Stephanie
1	General	100
12	General	100
112.113	General	100
2	Clothing	100
22	Clothing	100
220.119	Clothing	100

[Greg_Deckler]

@MrsGG OK, if I am understanding what you are saying, the probable "best practice" answer would be to have a dimension for your "Products" that would include the ID and Name and maybe some other information. You would then form a relationship between that table and your fact table. This is the classic "star schema" approach.

Instead, you could just create a column in your fact table. Here is one way:

Column =
  VAR __ID = [ID] & ""
  VAR __First = LEFT( __ID, 1 )
  VAR __Result = 
    SWITCH( __First,
      "1", General,
      "2", Clothing,
      ...
    )
RETURN
  __Result

Is that what you are asking about?

[Greg_Deckler]

@MrsGG Greatly depends on what you are actually trying to do/accomplish. What is the end goal here?