Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Data Errors that need correction in script

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

...];

1 Solution

Accepted Solutions
effinty2112
Master
Master

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
11CutRR
21.5MachineSS
31MachineSS
410MachineRR
55MachineSR
68MachineSR
73MachineRR
89MachineSR
91PaintSS
103PaintRR

Cheers

Andrew

View solution in original post

2 Replies
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
Master
Master

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
11CutRR
21.5MachineSS
31MachineSS
410MachineRR
55MachineSR
68MachineSR
73MachineRR
89MachineSR
91PaintSS
103PaintRR

Cheers

Andrew