Data Errors that need correction in script

[Anonymous]

Hello all,

I Hope there is an easy solution to this problem.

So I am trying to change the Type of a clock-in. We have people who clock in to the wrong type occasionally, and i want the S to change to R.  the reason is because after the machine has been Set-up (S) then they should clock into run (R).  so anything after the first R I want to be considered as R.  This table is pulled from The database so I really can't go in manually and make those changes.

Thank you.

The example is as follows:

LOAD * INLINE [

Location, Hours, Type

Cut, 1, R

Machine,1.5, S

Machine,1, S

Machine,10, R

Machine,5, S

Machine, 8, S

Machine,3, R

Machine,9,S

Paint, 1, S

Paint, 3, R

...];

I would like to change into this:

LOAD * INLINE [

Location, Hours, Type, Type_Rev

Cut, 1, R, R

Machine,1.5, S, S

Machine,1, S, S

Machine,10, R, R

Machine,5, S, R

Machine, 8, S, R

Machine,3, R, R

Machine,9,S, R

Paint, 1, S, S

Paint, 3, R, R

...];

[marcus_sommer]

If I understand your question right you will need to look into the previous record to check if the values from the current record are correct. Then you could use: Peek() or Previous() ?

- Marcus

[effinty2112 · Accepted solution]

Hi Jacob,

Try

Data:

Load

*,

RowNo() as RowNo,

if(Location =Peek(Location), if(Peek(Type1) = 'R','R',Type),Type) as Type1;

LOAD * INLINE [

Location, Hours, Type

Cut, 1, R

Machine,1.5, S

Machine,1, S

Machine,10, R

Machine,5, S

Machine, 8, S

Machine,3, R

Machine,9,S

Paint, 1, S

Paint, 3, R

];

I added a row number to show this table in the correct sort order:

RowNo	Hours	Location	Type	Type1
1	1	Cut	R	R
2	1.5	Machine	S	S
3	1	Machine	S	S
4	10	Machine	R	R
5	5	Machine	S	R
6	8	Machine	S	R
7	3	Machine	R	R
8	9	Machine	S	R
9	1	Paint	S	S
10	3	Paint	R	R

Cheers

Andrew