Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
djih
Partner
Partner

extract values from field names to transform table

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_KEYX-Color-NoX-Color-descX-Form-NoX-Form-descX-Weight-NoX-Weight-descY-Service-NoY-Service-descZ-Production-NoZ-Production-descZ-Purchasing-NoZ-Purchasing-desc
100011red2round1heavy6full service1yes2no
100022green1square----2no1yes

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_KEYTypeAttributeAttributeValueAttributeValueDesc
10001XColor1red
10001XForm2round
10001XWeight1heavy
10001YService6no service
10001ZProduction1yes
10001ZPurchasing2no
10002XColor2green
10002XForm1square
10002XWeight--
10002YService--
10002ZProduction2no
10002ZPurchasing1yes

 

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?

Labels (3)
1 Solution

Accepted Solutions
Vegar
MVP
MVP

2 Replies
Vegar
MVP
MVP

See attached qvw

clipboard_image_0.png

clipboard_image_1.png

djih
Partner
Partner
Author

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