Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

jacobesalas
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
Not applicable

Re: Data Errors that need correction in script

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

2 Replies
marcus_sommer
Not applicable

Re: Data Errors that need correction in script

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
Not applicable

Re: Data Errors that need correction in script

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