Visokio website     Downloads     Video tutorials     KnowledgeBase  
Scripting: Copying values from above if null? - Visokio Forums
Scripting: Copying values from above if null?
  •     SimonWiggins October 23, 2013 12:17PM

    I've been trying to find a way to automate copying values down from above if the row is null.
    The problem I have is that you can't execute 'for' or 'while' loops in functions in the field organiser (short of scripting)

    For example in a column I have:

    (where the nulls are empty cells)

    I know I can use something of the form:

    "Error field may contain more than 6 empty rows"),"Error")

    but the limitations of this are if the latest data is pulled in and has more than 6 nulls before a value is above then it will print the error. Obviously also it creates an unnecessary extra field and the old one needs to be deleted in a second Field Organiser. Obviously I could copy paste and edit it up to 100 or more but this isn't very elegant.

    I know I can use the Custom Script block to write a JavaScript function and as such have been learning javascript but am having a hard time applying it to my problem.

    My question is:
    Is there an easy/elegant way of doing this in formulas that I have overlooked?
    Or is it a case of using JavaScript to solve my problems?
    Is it better done in a 'script' fomula or in a 'Custom script' block bearing in mind I want to do it for 3 columns and keep the rest of the data in line?

    I've attached a sort of example of what I mean of some basic data and how I would and have solved it in the past but not copied the formula down long enough therefore it errors after 6 nulls.

    Hopefully this makes some sort of sense to someone! Thanks in advance for absolutely any help!

    Example.csv 199B
    Example.iok 8K
  •     michael October 24, 2013 12:41PM
    Hi Simon,

    It looks as following script can help (though it can run slow on bigger data):

    var val = "";
    do {
    val = formula("CELL([Field1],"+currentrow+")");
    } while(!val && currentrow>0)

    `, "currentrow", CURRENTROW())

    Please double check that this is the expected result.

    See also attached file (use .csv file from previous file if needed).
  •     SimonWiggins October 28, 2013 6:50AM
    Hi Michael,

    Thank you very much for this. I've got a grip on javascript now thanks to online courses (Codeacademy) but the helping hand is much appreciated as I was having trouble using applying what I'd learnt.

    The data set isn't very big for now so this is perfect and hopefully I can tweak it to be more efficient or better for larger datasets should I encounter any problems.

    Again many thanks!



It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership