extract values from field names to transform table

djih — Sat, 16 Nov 2024 02:24:28 GMT

Hi together,

I have the following challenge. I need to create a new structue to analyze some attributes. Therefore i need to extract values form the fieldnames from the given raw data. Since there are several houndreds of posible attributes i need a dynamic script or loop function. for better understanding here is an example.

my data source gives the following table structure and i can't change it:

Article_KEY	X-Color-No	X-Color-desc	X-Form-No	X-Form-desc	X-Weight-No	X-Weight-desc	Y-Service-No	Y-Service-desc	Z-Production-No	Z-Production-desc	Z-Purchasing-No	Z-Purchasing-desc
10001	1	red	2	round	1	heavy	6	full service	1	yes	2	no
10002	2	green	1	square	-	-	-	-	2	no	1	yes

Explaination: The fieldnames are assembeled as follows:

the first letter (X,Y,Z) describes he type of attribute,
2nd part ist the name of an attribute ("Color" or "Weight")
last part is the number (no) or the description (desc) of the actual value. (each value has a number and e description)

The target structure should be something like this:

Article_KEY	Type	Attribute	AttributeValue	AttributeValueDesc
10001	X	Color	1	red
10001	X	Form	2	round
10001	X	Weight	1	heavy
10001	Y	Service	6	no service
10001	Z	Production	1	yes
10001	Z	Purchasing	2	no
10002	X	Color	2	green
10002	X	Form	1	square
10002	X	Weight	-	-
10002	Y	Service	-	-
10002	Z	Production	2	no
10002	Z	Purchasing	1	yes

I wanted to try something like this.

For Each vFieldName in (dynamic list of fieldnames without Article_KEY)): Load Article_KEY, Subfield(vFieldName,'-',1) as Type, Subfield(vFieldName,'-',2) as Attribute, if(wildmatch(vFieldName,'*_No'),vFieldName as AttributeValue, if(wildmatch(vFieldName,'*_desc'),vFieldName as AttributeValueDesc

But i have no clue how to get the vFieldname list and navigate through all fields.

I hope you can understand the issue?

Re: extract values from field names to transform table

Vegar — Tue, 24 Sep 2019 11:31:41 GMT

See attached qvw

Re: extract values from field names to transform table

djih — Wed, 25 Sep 2019 07:52:37 GMT

It seems I was thinking too complicated. I wouldn't have thought that it would be as easy as that. Thank you very much!

topic Re: extract values from field names to transform table in QlikView

extract values from field names to transform table

Re: extract values from field names to transform table

Re: extract values from field names to transform table