Hello,
I found a discussion from 2015 (subject "sumif") that has a very helpful script for my current task.I wrote there my question but I don't know if people who participated in that discussion can see my question.
This script I found (slightly modified) and it works great for counting columns for each row where values are "0/0" (or whatever).
dt << New Column("Sum",
Numeric, Formula(
Local({i = 0, n = N Col(Current Data Table()) - 1},
Sum(Repeat({i++ ; (Column(i)[] =="0/0")}, n))
)
)
);However, how to edit this script to count columns that Contain "some_character"?
I have attached a data table where the last column "Sum" has a formula and needed resulted numbers.
I have a big data table and would like to have a script for counting columns for each row with specific values (as character).
I tried to modify the script above but it doesn't work:
dt << New Column("Miss",
Numeric, Formula(
Local({i = 0, n = N Col(Current Data Table()) - 1},
Sum(Repeat({i++ ; (Contains(Column(i)[],"."))}, n))
)
)
);</CODE>Please, help</P><P>Valentina</P></LINGO-BODY><LINGO-LABS id="lingo-labs-737490" slang="en-US" mode="CREATE"><LINGO-LABEL>Automation and Scripting</LINGO-LABEL></LINGO-LABS><LINGO-SUB id="lingo-sub-737514" slang="en-US" mode="CREATE">Re: sumif: count columns if they contain some character</LINGO-SUB><LINGO-BODY id="lingo-body-737514" slang="en-US" mode="CREATE"><P>Do you need to have a formula for this?</P></LINGO-BODY><LINGO-SUB id="lingo-sub-737576" slang="en-US" mode="CREATE">Re: sumif: count columns if they contain some character</LINGO-SUB><LINGO-BODY id="lingo-body-737576" slang="en-US" mode="CREATE"><P>Sorry, I don't understand your question.</P><P>I have a table ~500,000 rows and ~3,000 columns. I need to calculate Sum across columns that contain a dot - cant use formula. . </P><P>I want to have a script, any script that would work. I tried different approaches but none worked. .</P></LINGO-BODY><LINGO-SUB id="lingo-sub-737577" slang="en-US" mode="CREATE">Re: sumif: count columns if they contain some character</LINGO-SUB><LINGO-BODY id="lingo-body-737577" slang="en-US" mode="CREATE"><P>This is from my log:</P><P> </P><P>Column Sum Formula Interrupted<BR />argument is wrong type at row 421001 in access or evaluation of 'Contains' , Bad Argument( Column( i )[] ), Contains/*###*/(Column( i )[], ".")<BR />Formula evaluation errors have been ignored</P></LINGO-BODY><LINGO-SUB id="lingo-sub-737582" slang="en-US" mode="CREATE">Re: sumif: count columns if they contain some character</LINGO-SUB><LINGO-BODY id="lingo-body-737582" slang="en-US" mode="CREATE"><P>Most likely your loop is reaching some column which isn't character column. This part most likely needs some modification</P>
<PRE><CODE class=" language-jsl">n = N Col(Current Data Table()) - 1
how it should be done is very difficult to know without any knowledge of your data. It could be just a simple modification to the -1, getting list of character columns or calculating which columns to go through by some sort of a pattern
I have attached a small data table to my post. It has 3 rows and 3 cols with a header:
S1 S2 S3
0/0 ./. 0/0
./. ./. ./.
0/0 0/0 0/0
The script doesn't work in this sample table
Column sum Formula Interrupted
argument is wrong type at row 1 in access or evaluation of 'Contains' , Bad Argument( Column( i )[] ), Contains/*###*/(Column( i )[], ".")
Formula evaluation errors have been ignored
The original data table you attached wasn't like that as it has 4 columns (you cannot ignore Sum column)
Depending on your data, this is how I would do this if I were to use a Formula (most likely I would avoid that and just use a pure script solution)
Names Default To Here(1);
dt = Open("$DOWNLOADS/sum_help_data.jmp");
dt << Delete Column("Sum");
dt << New Column("Miss", Numeric, Continuous, Formula(
As Constant(
cols_of_interest = Filter Each({colname}, Current Data Table() << Get Column Names("String"),
!IsMissing(Regex(colname, "S\d"));
);
);
Local({
res = 0;
},
For Each({colname}, cols_of_interest,
res = res + (Contains(Column(colname)[], ".") > 0);
);
res;
);
));
Yes, it seems all data should be the same type.
Thank you - it works!
