Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
jacobesalas
Contributor III
Contributor III

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
MVP & Luminary
MVP & Luminary

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

View solution in original post