Looping through an example to generate more examples

amiller17
amiller17 New Altair Community Member
edited November 5 in Community Q&A

My data is confidential I cannot post it. 

Here is an example of what my data looks like:

|empty|attribute1|attribute2|attribute3|attribute4|attribute5|attribute6|attribute7|attribute8|attribute9|attribute10|empty|attribute1|attribute2|attribute3|attribute4|attribute5|attribute6|attribute7|attribute8|attribute9|attribute10|empty|attribute1|attribute2|attribute3|attribute4|attribute5|attribute6|attribute7|attribute8|attribute9|attribute10|empty|attribute1|attribute2|attribute3|attribute4|attribute5|attribute6|attribute7|attribute8|attribute9|attribute10|empty|attribute1|attribute2|attribute3|attribute4|attribute5|attribute6|attribute7|attribute8|attribute9|attribute10|empty|attribute1|attribute2|attribute3|attribute4|attribute5|attribute6|attribute7|attribute8|attribute9|attribute10|empty|attribute1|attribute2|attribute3|attribute4|attribute5|attribute6|attribute7|attribute8|attribute9|attribute10|empty|attribute1|attribute2|attribute3|attribute4|attribute5|attribute6|attribute7|attribute8|attribute9|attribute10|empty|attribute1|attribute2|attribute3|attribute4|attribute5|attribute6|attribute7|attribute8|attribute9|attribute10|empty|attribute1|attribute2|attribute3|attribute4|attribute5|attribute6|attribute7|attribute8|attribute9|attribute10|empty|attribute1|attribute2|attribute3|attribute4|attribute5|attribute6|attribute7|attribute8|attribute9|attribute10|empty|attribute1|attribute2|attribute3|attribute4|attribute5|attribute6|attribute7|attribute8|attribute9|attribute10|empty|attribute1|attribute2|attribute3|attribute4|attribute5|attribute6|attribute7|attribute8|attribute9|attribute10|empty|attribute1|attribute2|attribute3|attribute4|attribute5|attribute6|attribute7|attribute8|attribute9|attribute10|

I would like to transform it into:

|empty|attribute1|attribute2|attribute3|attribute4|attribute5|attribute6|attribute7|attribute8|attribute9|attribute10|

|empty|attribute1|attribute2|attribute3|attribute4|attribute5|attribute6|attribute7|attribute8|attribute9|attribute10|

|empty|attribute1|attribute2|attribute3|attribute4|attribute5|attribute6|attribute7|attribute8|attribute9|attribute10|

|empty|attribute1|attribute2|attribute3|attribute4|attribute5|attribute6|attribute7|attribute8|attribute9|attribute10|

|empty|attribute1|attribute2|attribute3|attribute4|attribute5|attribute6|attribute7|attribute8|attribute9|attribute10|

|empty|attribute1|attribute2|attribute3|attribute4|attribute5|attribute6|attribute7|attribute8|attribute9|attribute10|

|empty|attribute1|attribute2|attribute3|attribute4|attribute5|attribute6|attribute7|attribute8|attribute9|attribute10|

 

Please help me do this.

 

Best Answers

  • Thomas_Ott
    Thomas_Ott New Altair Community Member
    Answer ✓

    @amiller17 its hard for me to load this in at the moment but I did take a look at the the data structure. If every 11 rows/examples it changes in column lenght, you will need to do an interative loop with a Filter Range to first select out those rows. THEN i would use a Select Attributes operator to select the missing columns, make it invert and toss them out. Then use the Windowing operator set at 11 to select Data and Att0 through Att9. I think you'll need to change the series ecoding to 'attributes'. Do this all in side the loop and then put a Merge/Append operator outside the loop. 

  • amiller17
    amiller17 New Altair Community Member
    Answer ✓

    I've hacked together a solution using remember and recall. It's not pretty, but it works. I've attached it here for your perusal:

     

Answers

  • Thomas_Ott
    Thomas_Ott New Altair Community Member

    @amiller17 I would try the Windowing operator from the Value Series extension. Set the Window to 11.

  • amiller17
    amiller17 New Altair Community Member

    I have tried that, but the operator creates a matrix of missing values (?) which is not very helpful.

  • sgenzer
    sgenzer
    Altair Employee

    hi @amiller17 welcome to the community. I read your post several times but could not get my head around what your example was. Perhaps put your "sample" in a excel sheet with "before" and "after" tabs?

     

    Scott

     

  • amiller17
    amiller17 New Altair Community Member

    I do not know how to edit my original post but here is an excel spreadsheet with what I am looking to do. Note the tabs before and after. Thank you very much for your assistance. Notes about the example in the spreadsheet:

    Date and attr1-9 are numbers, ? is a missing value. Every 11 cells is a new example, and I have 49 rows like this one of variable length (from 11*4(44) to 11*300(3,300) cells in length) to process.  

  • amiller17
    amiller17 New Altair Community Member

    I do not know how to edit my original post but here is an excel spreadsheet with what I am looking to do. Note the tabs before and after. Thank you very much for your assistance. Notes about the example in the spreadsheet:

    Date and attr1-9 are numbers, ? is a missing value. Every 11 cells is a new example, and I have 49 rows like this one of variable length (from 11*4(44) to 11*300(3,300) cells in length) to process.  

     

    I never attached the file, my bad: https://docs.google.com/spreadsheets/d/1PSKu7wZ4gXnqQaGXGUiCKkQepPFHXX3d4yFcGHI4qZw/edit?usp=sharing

     

    I tried sharing the .xlsx but the forums wouldn't let me upload the data.

  • Thomas_Ott
    Thomas_Ott New Altair Community Member
    Answer ✓

    @amiller17 its hard for me to load this in at the moment but I did take a look at the the data structure. If every 11 rows/examples it changes in column lenght, you will need to do an interative loop with a Filter Range to first select out those rows. THEN i would use a Select Attributes operator to select the missing columns, make it invert and toss them out. Then use the Windowing operator set at 11 to select Data and Att0 through Att9. I think you'll need to change the series ecoding to 'attributes'. Do this all in side the loop and then put a Merge/Append operator outside the loop. 

  • amiller17
    amiller17 New Altair Community Member

    Is there a way to receive the leftovers from a filter-range as an output?

  • sgenzer
    sgenzer
    Altair Employee

    ok thanks @amiller17. This looks like a Scott ETL puzzle. :) @Thomas_Ott's solution will definitely work but I wonder if there's a more elegant solution...stay tuned.

     

    Scott

     

  • amiller17
    amiller17 New Altair Community Member
    Answer ✓

    I've hacked together a solution using remember and recall. It's not pretty, but it works. I've attached it here for your perusal:

     

  • sgenzer
    sgenzer
    Altair Employee

    haha yep @amiller17 that will work too. I tried to find something elegant with pivot / depivot plus transpose but could not get it. Looks so simple....

     

    Glad you found a working hack.


    Scott