Resolving multiple (bool) columns into one field

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Resolving multiple (bool) columns into one field

marcus_sommer — Tue, 11 Apr 2017 17:07:45 GMT

I would probably transform the Tbl_Data perThe Crosstable Load and then joining/mapping or associating it with the Tbl_Attributes.

- Marcus

topic Re: Resolving multiple (bool) columns into one field in QlikView

Resolving multiple (bool) columns into one field

Re: Resolving multiple (bool) columns into one field