Forum Discussion
Reference 1st digit in column
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
3 Replies
MrsGG Greatly depends on what you are actually trying to do/accomplish. What is the end goal here?
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 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 __ResultIs that what you are asking about?
