There are many rows of data in a database table, each of which contains many columns. ID (numeric - 12345, 12378, etc) Name 1 - AAA,BBB,CCC Name 2 - dd, ee, ff Name 3 - Ggg, Hhh, Iii

The objective is to come up with rules based on a combination of these values to map to a set of groups. Group ID 1 - 1,000

There is a large mapping table that applies rules to the individual records and maps each one to it's group ID. The code is spaghetti and has gaps and duplications, and I would like a better approach if you can suggest one.

Here is a small example:

if Name 1 in (...) then use this process below:

if ID = 856743 THEN Group ID = 2000

if ID in (1,2,3... 200) THEN Group ID = 2001

if ID = 377 OR Name 2 in (X,Y,Z) THEN Group ID = 2003

if Name 2 in (U, V, W) THEN Group ID = 2007

Else Group ID = 2011

I am thinking that I need to take all the column permutations and make them dimensions. The problem is there are many segments of mapping like this: if a column value is in (X,Y,Z) and the ID is one of (a large set) then the value is ...

From time to time more IDs are added, and who knows if new column values are added.

Should I just have a mapping table listing: ID, and each dimension, and what the Group ID is? That would necessitate adding new mapping rows when new IDs are added. The current process sometimes handled that automatically because it just takes a record that matches Name 1 and Name 2,

ID N1 N2 Group ID 1 1 1 10 2 1 1 11 2 1 2 12

  • 2 1 13
  • 2 2 14


  • 2
    $\begingroup$ Welcome to SciComp.SE. This site is about Computational Science. Your questions does not seem to be related to it, so it seems to be off-topic here. $\endgroup$ – nicoguaro Apr 6 at 21:46
  • $\begingroup$ Then where is the appropriate location? According to this response, this is the correct location: meta.stackexchange.com/questions/139176/… $\endgroup$ – Intrafinesse Apr 7 at 0:38
  • 1
    $\begingroup$ I am not sure, maybe it is on-topic on GIS. Have you asked in their Meta site? $\endgroup$ – nicoguaro Apr 7 at 2:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.