Hello everyone,
I've spent some time browsing through the forum but couldn't find a solution for my problem.
I'm connecting to a Oracle database to consum among others 2 tables:
Tbl_Data:
| ID | A1 | A2 | A3 | A4 |
|---|
| 1 | true | (null) | true | Germany |
| 2 | true | true | (null) | Spain |
Tbl_Attributes:
| ID | Name | DataCOL |
|---|
| 1 | Area.Highway | a1 |
| 2 | Weather.Sunny | a2 |
| 3 | Weather.Rain | a3 |
| 4 | Country | a4 |
Almost all columns in Tbl_Data are something A1-A(n) with either a boolean value or a string. Tbl_Attributes is providing a mapping for A(n) to actual column name/content. Especially demanding is the fact that those tables still dynamically grow and I can't really map it hardcoded i.e. not all A(n) columns are already assigned in Tbl_Attributes.
As of now I'm checking wether a column has a true value via if Statements and then joining belonging entries into one field:
//Weather
If(A5 = 'true', 'Clear',
If(A11 = 'true', 'Cloudy',
If(A12 = 'true', 'Rain',
If(A16 = 'true', 'Spray',
If(A17 = 'true', 'Sun',
If(A18 = 'true', 'Fog',
If(A19 = 'true', 'Snow',
If(A20 = 'true', 'Extreme'
)))))))) As Weather
In addition to that I also map columns with string values to Display the correct field name:
Mapping:
Mapping Load
DataCOL,
NAME;
SQL SELECT DataCOL,
NAME
FROM Tbl_Attributes;
Rename Fields using Mapping;
So my question: is there anyway to map this in a dynamic way based on Tbl_Attributes and also join columns with true into one field e.g. Weather.Rain / Weather.Snow to a field Weather containing: rain, snow etc.
Thanks in advance
