topic extract values from field names to transform table in QlikView App Dev
https://community.qlik.com/t5/QlikView-App-Dev/extract-values-from-field-names-to-transform-table/m-p/1627369#M594562
<P>Hi together,</P><P> 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.</P><P>my data source gives the following table structure and i can't change it:</P><TABLE><TBODY><TR><TD>Article_KEY</TD><TD>X-Color-No</TD><TD>X-Color-desc</TD><TD>X-Form-No</TD><TD>X-Form-desc</TD><TD>X-Weight-No</TD><TD>X-Weight-desc</TD><TD>Y-Service-No</TD><TD>Y-Service-desc</TD><TD>Z-Production-No</TD><TD>Z-Production-desc</TD><TD>Z-Purchasing-No</TD><TD>Z-Purchasing-desc</TD></TR><TR><TD>10001</TD><TD>1</TD><TD>red</TD><TD>2</TD><TD>round</TD><TD>1</TD><TD>heavy</TD><TD>6</TD><TD>full service</TD><TD>1</TD><TD>yes</TD><TD>2</TD><TD>no</TD></TR><TR><TD>10002</TD><TD>2</TD><TD>green</TD><TD>1</TD><TD>square</TD><TD>-</TD><TD>-</TD><TD>-</TD><TD>-</TD><TD>2</TD><TD>no</TD><TD>1</TD><TD>yes</TD></TR></TBODY></TABLE><P>Explaination: The fieldnames are assembeled as follows:</P><UL><LI>the first letter (X,Y,Z) describes he type of attribute,</LI><LI>2nd part ist the name of an attribute ("Color" or "Weight")</LI><LI>last part is the number (no) or the description (desc) of the actual value. (each value has a number and e description)</LI></UL><P>The target structure should be something like this:</P><TABLE><TBODY><TR><TD>Article_KEY</TD><TD>Type</TD><TD>Attribute</TD><TD>AttributeValue</TD><TD>AttributeValueDesc</TD></TR><TR><TD>10001</TD><TD>X</TD><TD>Color</TD><TD>1</TD><TD>red</TD></TR><TR><TD>10001</TD><TD>X</TD><TD>Form</TD><TD>2</TD><TD>round</TD></TR><TR><TD>10001</TD><TD>X</TD><TD>Weight</TD><TD>1</TD><TD>heavy</TD></TR><TR><TD>10001</TD><TD>Y</TD><TD>Service</TD><TD>6</TD><TD>no service</TD></TR><TR><TD>10001</TD><TD>Z</TD><TD>Production</TD><TD>1</TD><TD>yes</TD></TR><TR><TD>10001</TD><TD>Z</TD><TD>Purchasing</TD><TD>2</TD><TD>no</TD></TR><TR><TD>10002</TD><TD>X</TD><TD>Color</TD><TD>2</TD><TD>green</TD></TR><TR><TD>10002</TD><TD>X</TD><TD>Form</TD><TD>1</TD><TD>square</TD></TR><TR><TD>10002</TD><TD>X</TD><TD>Weight</TD><TD>-</TD><TD>-</TD></TR><TR><TD>10002</TD><TD>Y</TD><TD>Service</TD><TD>-</TD><TD>-</TD></TR><TR><TD>10002</TD><TD>Z</TD><TD>Production</TD><TD>2</TD><TD>no</TD></TR><TR><TD>10002</TD><TD>Z</TD><TD>Purchasing</TD><TD>1</TD><TD>yes</TD></TR></TBODY></TABLE><P> </P><P>I wanted to try something like this.</P><P> </P><P> </P><LI-CODE lang="markup">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</LI-CODE><P> </P><P> </P><P> But i have no clue how to get the vFieldname list and navigate through all fields.</P><P>I hope you can understand the issue?</P>Tue, 24 Sep 2019 10:44:33 GMTdjih2019-09-24T10:44:33Zextract values from field names to transform table
https://community.qlik.com/t5/QlikView-App-Dev/extract-values-from-field-names-to-transform-table/m-p/1627369#M594562
<P>Hi together,</P><P> 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.</P><P>my data source gives the following table structure and i can't change it:</P><TABLE><TBODY><TR><TD>Article_KEY</TD><TD>X-Color-No</TD><TD>X-Color-desc</TD><TD>X-Form-No</TD><TD>X-Form-desc</TD><TD>X-Weight-No</TD><TD>X-Weight-desc</TD><TD>Y-Service-No</TD><TD>Y-Service-desc</TD><TD>Z-Production-No</TD><TD>Z-Production-desc</TD><TD>Z-Purchasing-No</TD><TD>Z-Purchasing-desc</TD></TR><TR><TD>10001</TD><TD>1</TD><TD>red</TD><TD>2</TD><TD>round</TD><TD>1</TD><TD>heavy</TD><TD>6</TD><TD>full service</TD><TD>1</TD><TD>yes</TD><TD>2</TD><TD>no</TD></TR><TR><TD>10002</TD><TD>2</TD><TD>green</TD><TD>1</TD><TD>square</TD><TD>-</TD><TD>-</TD><TD>-</TD><TD>-</TD><TD>2</TD><TD>no</TD><TD>1</TD><TD>yes</TD></TR></TBODY></TABLE><P>Explaination: The fieldnames are assembeled as follows:</P><UL><LI>the first letter (X,Y,Z) describes he type of attribute,</LI><LI>2nd part ist the name of an attribute ("Color" or "Weight")</LI><LI>last part is the number (no) or the description (desc) of the actual value. (each value has a number and e description)</LI></UL><P>The target structure should be something like this:</P><TABLE><TBODY><TR><TD>Article_KEY</TD><TD>Type</TD><TD>Attribute</TD><TD>AttributeValue</TD><TD>AttributeValueDesc</TD></TR><TR><TD>10001</TD><TD>X</TD><TD>Color</TD><TD>1</TD><TD>red</TD></TR><TR><TD>10001</TD><TD>X</TD><TD>Form</TD><TD>2</TD><TD>round</TD></TR><TR><TD>10001</TD><TD>X</TD><TD>Weight</TD><TD>1</TD><TD>heavy</TD></TR><TR><TD>10001</TD><TD>Y</TD><TD>Service</TD><TD>6</TD><TD>no service</TD></TR><TR><TD>10001</TD><TD>Z</TD><TD>Production</TD><TD>1</TD><TD>yes</TD></TR><TR><TD>10001</TD><TD>Z</TD><TD>Purchasing</TD><TD>2</TD><TD>no</TD></TR><TR><TD>10002</TD><TD>X</TD><TD>Color</TD><TD>2</TD><TD>green</TD></TR><TR><TD>10002</TD><TD>X</TD><TD>Form</TD><TD>1</TD><TD>square</TD></TR><TR><TD>10002</TD><TD>X</TD><TD>Weight</TD><TD>-</TD><TD>-</TD></TR><TR><TD>10002</TD><TD>Y</TD><TD>Service</TD><TD>-</TD><TD>-</TD></TR><TR><TD>10002</TD><TD>Z</TD><TD>Production</TD><TD>2</TD><TD>no</TD></TR><TR><TD>10002</TD><TD>Z</TD><TD>Purchasing</TD><TD>1</TD><TD>yes</TD></TR></TBODY></TABLE><P> </P><P>I wanted to try something like this.</P><P> </P><P> </P><LI-CODE lang="markup">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</LI-CODE><P> </P><P> </P><P> But i have no clue how to get the vFieldname list and navigate through all fields.</P><P>I hope you can understand the issue?</P>Tue, 24 Sep 2019 10:44:33 GMThttps://community.qlik.com/t5/QlikView-App-Dev/extract-values-from-field-names-to-transform-table/m-p/1627369#M594562djih2019-09-24T10:44:33ZRe: extract values from field names to transform table
https://community.qlik.com/t5/QlikView-App-Dev/extract-values-from-field-names-to-transform-table/m-p/1627384#M594563
<P>See attached qvw</P><P><IMG src="https://community.qlik.com/t5/image/serverpage/image-id/19935i03697189ABDF997B/image-size/large?v=1.0&px=-1" border="0" title="clipboard_image_0.png" alt="clipboard_image_0.png" /></P><P><IMG src="https://community.qlik.com/t5/image/serverpage/image-id/19936i08C3CC124E9E4B1A/image-size/large?v=1.0&px=-1" border="0" title="clipboard_image_1.png" alt="clipboard_image_1.png" /></P>Tue, 24 Sep 2019 11:31:41 GMThttps://community.qlik.com/t5/QlikView-App-Dev/extract-values-from-field-names-to-transform-table/m-p/1627384#M594563Vegar2019-09-24T11:31:41ZRe: extract values from field names to transform table
https://community.qlik.com/t5/QlikView-App-Dev/extract-values-from-field-names-to-transform-table/m-p/1627958#M594564
<P>It seems I was thinking too complicated. I wouldn't have thought that it would be as easy as that. Thank you very much!</P>Wed, 25 Sep 2019 07:52:37 GMThttps://community.qlik.com/t5/QlikView-App-Dev/extract-values-from-field-names-to-transform-table/m-p/1627958#M594564djih2019-09-25T07:52:37Z