topic Re: How to Select If Column Contains a Specific Value Else Do Nothing in Discussions

How to Select If Column Contains a Specific Value Else Do Nothing

Sully — Fri, 17 Aug 2018 14:22:27 GMT

Hello Everyone,

Is it possible to write a select statement for a column where if the column does not contain a desired value it will do nothing but if the column does contain the value I am looking for I can have it perform additional scripting?

I have a column Notification Code that contains 3 values "1-Addition", "3-Update", and "4-Delete" that is among my transactional data. When the table is updated and there happens to be a "4-Delete" in the column I currently subset the data and then match/update it back to the original file in order to determine the first instance (i.e the Addition or Update) and delete both records of the transaction out of the table. Today is the first instance where the data was updated and didn't contain a 4-Delete. This caused my select and delete statement to delete all records out of the table. Example of current JSL for Select and Delete below:

rowchoice = dt3 << Get Rows Where (:Notification Code == "4-Delete");
dt3 << Select Rows (rowchoice);
subdt1 = dt3 << Subset(
       Link to Original Data Table (0),
       Rows(),
       Output Table Name ("Deletions")
       );
      
subdt1.2 = Current Data Table ();
subdt1.2 << Delete Column (:N Rows);
subdt1.2 << New Column ("Deletions", Numeric, Continous,
<< set each value(1)
);
 
dt3 << Update (
       With (Data Table ("Deletions")),
       Match Columns (:ROL Case # == :ROL Case #, :"Card/Account Number" == :"Card/Account Number"),
       Selected (:Deletions)
);
 
Close(subdt1.2, No Save);
rowchoice = dt3 << get rows where (:Deletions == 1);
dt3 << Select Rows(rowchoice);
dt3 << Delete Rows (rowchoice);
dt3 << Delete Column (:Deletions);

Any help is greatly appreciated.

Re: How to Select If Column Contains a Specific Value Else Do Nothing

txnelson — Fri, 17 Aug 2018 15:17:59 GMT

From what I can tell, the following will work

 
rowchoice = dt3 << Get Rows Where (:Notification Code == "4-Delete");
If( N Rows( rowchoice ) > 0,
dt3 << Select Rows (rowchoice);
subdt1 = dt3 << Subset(
       Link to Original Data Table (0),
       Rows(),
       Output Table Name ("Deletions")
       );
      
subdt1.2 = Current Data Table ();
subdt1.2 << Delete Column (:N Rows);
subdt1.2 << New Column ("Deletions", Numeric, Continous,
<< set each value(1)
);
 
dt3 << Update (
       With (Data Table ("Deletions")),
       Match Columns (:ROL Case # == :ROL Case #, :"Card/Account Number" == :"Card/Account Number"),
       Selected (:Deletions)
);
 
Close(subdt1.2, No Save);
rowchoice = dt3 << get rows where (:Deletions == 1);
dt3 << Select Rows(rowchoice);
dt3 << Delete Rows (rowchoice);
dt3 << Delete Column (:Deletions);
);

Re: How to Select If Column Contains a Specific Value Else Do Nothing

Sully — Fri, 17 Aug 2018 16:38:53 GMT

That worked great! Thank you for the help!