Skip to main content
Question

Convert excelsheet format by unique value

L
Forum|alt.badge.img

Hi guys,

I have a lookup table contains values in different fields (column), example as below.

Is there a way I can make the all values become field names then give yes/no in each fields of the feature class?

Feature Class Field 1 Field 2 Field 3 contour_height_pts surveydate elevation vert_datum contour_minor surveydate elevation vert_datum contour_major surveydate elevation vert_datum Drainage feature linetype instaldate modifydate Culvert linetype instaldate modifydate

Thank you

 

    fmelizard
    Contributor
    Forum|alt.badge.img+17
    • fmelizardContributor
    • Contributor
    • December 13, 2017
    Hi @lisasays, do you have an example of the output you'd like to see? Would you have elevation as a field name once or multiple times? How would the Y/N be populated?

     

    L
    Forum|alt.badge.img
    fmelizard wrote:
    Hi @lisasays, do you have an example of the output you'd like to see? Would you have elevation as a field name once or multiple times? How would the Y/N be populated?

     

    Thank you Tia. I want elevation as a field name only once.. The output i want is as below

     

     

     

    takashi
    Supporter
    • takashiSupporter
    • December 14, 2017

    Hi @lisasays, this workflow example might help you.

    The point is to use the value of an attribute as a new attribute name to create desired attributes that store the value "Y".

    Result

    Hope this helps.

    takashi
    Supporter
    • takashiSupporter
    • December 14, 2017
    takashi wrote:

    Hi @lisasays, this workflow example might help you.

    0684Q00000ArLiDQAV.png

    The point is to use the value of an attribute as a new attribute name to create desired attributes that store the value "Y".

    0684Q00000ArLiHQAV.png

    Result

    0684Q00000ArLgOQAV.png

    Hope this helps.

    Another thought. JSON operations could be effective in this case.

     

    0684Q00000ArMouQAF.png

    JSON Template Expression:

     

    {
    fme:get-attribute("Field 1") : "Y",
    fme:get-attribute("Field 2") : "Y",
    fme:get-attribute("Field 3") : "Y"
}
    L
    Forum|alt.badge.img
    takashi wrote:
    Another thought. JSON operations could be effective in this case.

     

    0684Q00000ArMouQAF.png

    JSON Template Expression:

     

    {
    fme:get-attribute("Field 1") : "Y",
    fme:get-attribute("Field 2") : "Y",
    fme:get-attribute("Field 3") : "Y"
}
    Thanks a lot Takashi. Since my lookup table is quite big, is there a way it can automatic get the list for "Attribute to expose" in JSONFlattener?

     

     

    takashi
    Supporter
    • takashiSupporter
    • December 18, 2017
    lisasays wrote:
    Thanks a lot Takashi. Since my lookup table is quite big, is there a way it can automatic get the list for "Attribute to expose" in JSONFlattener?

     

     

    Unfortunately there is no way to expose attribute names automatically. However, if you have an external table (e.g. CSV or Excel spreadsheet) that contains all possible field names, you can add the AttributeExposer instead of setting the Attributes to Expose parameter in the JSONFlattener and import the field names from the table.

     

     

    takashi
    Supporter
    • takashiSupporter
    • December 18, 2017
    takashi wrote:

    Hi @lisasays, this workflow example might help you.

    0684Q00000ArLiDQAV.png

    The point is to use the value of an attribute as a new attribute name to create desired attributes that store the value "Y".

    0684Q00000ArLiHQAV.png

    Result

    0684Q00000ArLgOQAV.png

    Hope this helps.

    This workflow would be robuster. Assuming that names of the target attributes are formatted in "Field<space><one or more digits>". Note: If "Field *" attribute can contain <empty> or <null>, remove them (i.e. change them to <missing>) with the NullAttributeMapper before the ListExpressionPopulator.

     

    0684Q00000ArMozQAF.png

     

    JSON Template Expression

     

    {|
    for $name in fme:get-list-attribute("_list{}")
    return {$name : "Y"}
|}

     

    L
    Forum|alt.badge.img
    takashi wrote:
    This workflow would be robuster. Assuming that names of the target attributes are formatted in "Field<space><one or more digits>". Note: If "Field *" attribute can contain <empty> or <null>, remove them (i.e. change them to <missing>) with the NullAttributeMapper before the ListExpressionPopulator.

     

    0684Q00000ArMozQAF.png

     

    JSON Template Expression

     

    {|
    for $name in fme:get-list-attribute("_list{}")
    return {$name : "Y"}
|}

     

    Great thanks a lot! This perfectly solved my problem :)

     

     

    Reply


    Helpful Members This Week

    Community Stats

    31,031
    Posts
    117,604
    Replies
    38,810
    Members

    Latest FME

    Terms & ConditionsCookie settings

    Cookie policy

    We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

     
    Cookie settings
    A product of
    Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.