topic Re: Data Errors that need correction in script in QlikView

Data Errors that need correction in script

Anonymous — Thu, 30 Jun 2016 23:37:55 GMT

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

...];

Re: Data Errors that need correction in script

marcus_sommer — Fri, 01 Jul 2016 08:05:50 GMT

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

Re: Data Errors that need correction in script

effinty2112 — Fri, 01 Jul 2016 10:03:49 GMT

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