Choose Language Hide Translation Bar
Highlighted
jenny1
Level I

How to Script for columns with conditions

Hi, 

 

I need help scripting some conditions. I am attaching a sample data set and a word document with the conditions. I am able to do simple scripts but this one goes beyond my level of expertise :)

 

Thank you. 

 

2 REPLIES 2
Highlighted
uday_guntupalli
Level VIII

Re: How to Script for columns with conditions

@jenny1
         I tried to look through your question and understand what you are trying to do. 

         Let us try and outline the steps you want to acheive. Initially, you might want to start of with a flag that says, are there any rows where the column "Code" changes in 2 consecutive rows be it from "PD" to "HD" or vice-versa. 

Clear Log(); Clear Globals(); 

dt = Current Data Table(); 

// Identify rows where 2 consecutive rows change from PD to HD or vice-versa
dt << New Column("Match1",Character,Nominal,Formula(If(Row()==1,0,
														If(:Code[Row()-1] == :Code[Row()],
															0;
															,
															1;
														  )
													  )														
												   )
				);

I tried something like whats shown above. I would expect to see consecutive ones in the Match1 column if these conditions are satisfied - but I don't see any in your sample data set. Is this the direction you want to be going in ? 

 

 

Best
Uday
Highlighted
jenny1
Level I

Re: How to Script for columns with conditions

There are two parts to the problem. The first one is the "Result1" column. Based on the information from columns "Code," "Date," and "Cycle," and for each value of the column "Code" characterize the variation within cycles. So every time a new "PD" or "HD" cycle starts, the first month will be counted as one, and it will go to the last month within the same cycle to identify the difference. Based on that, it will rename the rows under Result1 "WI" if there are within the first three months of the cycle, and "WO" if there are the last three ones. Anything in between will be renamed as "Valid."  But this will only happen if there are at least seven months of difference between the cycle (for that specific "Code"), AND at least one row can be classified as "WI" (first 3 months), one as only "WO" (last 3 months), and if there is one that can be classified as "Valid"  (neither the first 3 months, nor the last 3 months within the cycle) . If that condition is not met, then Result1 can be labeled as "N/A".

 

The second part, Result2, is to identify those cycle switches from "HD" to " PD" and "PD" to HD" that occur for each value of the column "Code". Also, identify if it is the first time, the second time, ...., that the switch occurs within that "Code."

 

Thank you for your help, and please let me know if this answer clarifies your question.

Article Labels

    There are no labels assigned to this post.