BookmarkSubscribe
Choose Language Hide Translation Bar
ReginaHong
Occasional Contributor

To find the increase or decrease of values in each row in the same column

Hi,

 

The objective that I want to achieve in this task is if the values change from "1 to 2" or "2 to 1"  for 2 consecutive days in the Sigma column, then remove that two rows. Can It be done? 

Please refer attached my data set.

Thank you.

Eg1.JPG

 

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
ms
Super User ms
Super User

Re: To find the increase or decrease of values in each row in the same column

There are several ways to do this. Here's one example using JSL. It selects both rows that match the criteria. Un-comment the last line of code to delete the rows. Note that as rows are deleted new row pairs with "1 to 2" or "2 to 1" may be created, but the days will not be consecutive.

//Example Table
dt = New Table("untitled 28994",
    Add Rows(39),
    New Column("Day", Numeric, "Continuous", Format("Best", 12), Set Values(1 :: 39)),
    New Column("Sigma", Numeric, "Continuous", Set values(J(39, 1, Random Integer(0, 2))))
);


// Locate row sequence pairs which product = 2
n = N Row(dt);
m = Loc(:Sigma[1 :: n - 1] :* :Sigma[2 :: n] == 2);
dt << select rows(m |/ m + 1);
// dt << delete rows;

 

0 Kudos
4 REPLIES 4
txnelson
Super User

Re: To find the increase or decrease of values in each row in the same column

Of course it can be done....however, your description is a bit confusing to me. Could you please elaborate by indicating on your attached example, which of the Days would be removed?
Jim
0 Kudos
ReginaHong
Occasional Contributor

Re: To find the increase or decrease of values in each row in the same column

For example, in day 5 and 6, the sigma value has change from 1 to 2. Hence, to remove the rows in day 5 and 6. So it will keep checking for the rest of the rows and delete it if there are changes from 1 to 2 or 2 to 1.

 

Thank you.

0 Kudos
Highlighted
ms
Super User ms
Super User

Re: To find the increase or decrease of values in each row in the same column

There are several ways to do this. Here's one example using JSL. It selects both rows that match the criteria. Un-comment the last line of code to delete the rows. Note that as rows are deleted new row pairs with "1 to 2" or "2 to 1" may be created, but the days will not be consecutive.

//Example Table
dt = New Table("untitled 28994",
    Add Rows(39),
    New Column("Day", Numeric, "Continuous", Format("Best", 12), Set Values(1 :: 39)),
    New Column("Sigma", Numeric, "Continuous", Set values(J(39, 1, Random Integer(0, 2))))
);


// Locate row sequence pairs which product = 2
n = N Row(dt);
m = Loc(:Sigma[1 :: n - 1] :* :Sigma[2 :: n] == 2);
dt << select rows(m |/ m + 1);
// dt << delete rows;

 

0 Kudos
ReginaHong
Occasional Contributor

Re: To find the increase or decrease of values in each row in the same column

Hi,

 

Thank you for the solution below. However, I noticed that it has removed day 11 which the sigma is "0 to 2". But I only want to remove rows with sigma " 1 to 2" or "2 to 1" only. Please advise.

 

Thank you.

0 Kudos