Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
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
Partner
Partner

See attached qvw

clipboard_image_0.png

clipboard_image_1.png

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

2 Replies
Vegar
Partner
Partner

See attached qvw

clipboard_image_0.png

clipboard_image_1.png

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

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!