maintain reference code from database

JeRoen shared this idea 3 months ago
Awaiting Reply

Hello,

In our measure tables we have a lot of fields (100's) that are of type integer and that each number in each of those fields has a meaning. For every field a different meaning ofcourse.

For example a field like 'refusal type' might have like 3 options (1, 2, 3) each a description of a 'refusal type' like something as "is duplicate" or "missing mandatory data".

In another field like 'Gender' the options are 1, 2, 9 which mean the following, 1=Female, 2=Male and 9=Unknown.

We do have a datadictionary table that has for each registry and all field all possible value option with their descriptive value are stored. That looks like this (simplified) for the Gender field.

/de3691408a97ccb0804fe3ca9900f9c7Joining is not really an option because if I have like 20 fields I need 20 alias tables of the datadictionary table and different join strategies.

I would like to be able to create a referencecode for each field where I can put some SQL that fetches all integer values and their corresponding desciptive values for the required registry and field combinations from the datadictionary table and then in the report change the format to referencecode and that the desciptive value is shown instead of the number (much like they work now).

Currently there is an option to use CSV files for reference code but when the CSV is altered (manual action) the referrence code is not refreshed. With an little bit of SQL this can be checked with every run or every night or something like so.

Even better would be if you could choose SQL in the formatter of a field and then add the SQL the fetches the correct desciption for each value (kind of a lookup table).

The reason for this is that the option value of a field change relative often (once or twice a year) and the description values can be rather long so storing it in the measure field is not optimal.

If you can suggest other options to use that might make the management of this issue easier would be very helpfull.

Regards,

JeRoen

Comments (1)

photo
1

Hi JeRoen,

Thanks for reaching out and for your detailed use case! I can see the validity of this idea, but in terms of managing this issue through current available means, have you considered utilizing our ETL functionality? I'm afraid I can't really speak much to your data or reporting/business needs of course to say specifically what you'd need to do, and report building is more a Consulting gig, but in terms of potential options that are available now, aside from manually updating your CSV ref codes, it may be worth clicking the link to the Wiki above and checking out the Transformation Flows to see if one of these methods may be viable for you. Please take a look at that and let me know your thoughts, and if that doesn't work, I'm happy to submit this idea.

Regards,

Mike