topic Re: How to use load script to remove wrong data entry ? in QlikView

How to use load script to remove wrong data entry ?

paulyeo11 — Wed, 18 May 2016 06:36:17 GMT

Hi All

I have a raw data file , due to operator enter wrong year 2020 , now it affect my QV report. as i am not able to display all SOURCE compute by sales.

Does any one here know how to remove the particular data year = 2020 to 2010 ?

Paul

Re: How to use load script to remove wrong data entry ?

paulyeo11 — Wed, 18 May 2016 06:37:50 GMT

Hi All

I just notice that i typing wrong , what i mean is replace 2020 to 2010 for year.

Paul

Re: How to use load script to remove wrong data entry ?

Kushal_Chawda — Wed, 18 May 2016 06:41:53 GMT

In your source you can try below condition

LOAD *

FROM Source

Where YearField <= Year(Today());

Re: How to use load script to remove wrong data entry ?

Anonymous — Wed, 18 May 2016 06:48:05 GMT

you Need to test the date and adjust it

if (year(date) = 2020, addyears(date,-10), date) as date

if there are more wrong Dates you Need to add each date manually!!

-> But how do you know what the Operator made wrong?

can't you getthe data again with correct Input data?

Re: How to use load script to remove wrong data entry ?

paulyeo11 — Wed, 18 May 2016 06:59:16 GMT

Hi Kushal

When i run , i get error below :-

Cannot open file 'D:\AUTO\SOURCE

Where YearField <= Year'

LOAD *

FROM SOURCE

Where YearField <= Year(Today())

I try to change the SOURCE to GL_TABLE , it also cannot work.

Paul

Re: How to use load script to remove wrong data entry ?

paulyeo11 — Wed, 18 May 2016 07:08:05 GMT

Hi Sir

I try below script , no error but never change the year from 2020 to 2010.

if (year(@58:77T)=2020, addyears(@58:77T,-10), @58:77T) as date_,

i have enclosed my file , kindly unzip and copy to drive 😧 and copy to folder AUTO_

Paul !

Re: How to use load script to remove wrong data entry ?

Kushal_Chawda — Wed, 18 May 2016 07:10:10 GMT

Source here would be actual QVD from which Data is coming. If you don't have Year field in Your Data, try like below

LOAD *

FROM YourTable.qvd(qvd)

where year(DateField)<= year(today());

Re: How to use load script to remove wrong data entry ?

Anonymous — Wed, 18 May 2016 07:15:04 GMT

i cannot download your file (Company restriction)

but when I add a TextBox in your qvw it works

maybe a preload would work? please test

GL_TABLE:

load *,

addyears(date,-10) as newdate;

LOAD
'TDSS' as SOURCE_GL,
'60' as SOURCE,
date#(@58:77T,'DD/MM/YYYY') as date,
AutoNumberHash128(@58:77T,'60') As Link_GL_Key,
@124:129T as [Reporting Code],
if(@102:103T = '-',@86:101T*-1,@86:101T)*-1 as [Amount]
FROM $(vRAWPath)$(vFile50).txt (ansi, fix, no labels, header is 0, record is line);

Re: How to use load script to remove wrong data entry ?

paulyeo11 — Wed, 18 May 2016 07:16:46 GMT

Hi Rudolf

after reading your last 2 line of your suggestion.

you got the point, i should have ask the I.T staff to correct the error at input , since it is wrong data entry. As you know people will the I.T staff ask me back if there are too many error he will not able to write script and correct the mistake. So in order not to make thing worst , since this is small issue , so i just try to solve it.

Since this error not happen too often , and when it happen i will know it , so i can just correct it by adding script.

Paul

Re: How to use load script to remove wrong data entry ?

PrashantSangle — Wed, 18 May 2016 07:20:06 GMT

Hi,

If your date is in proper date datatype then try

if(Year(dateField)=2020,addYears(dateField,-10),dateField)

if not in date datatype then try

if(Year(date#(dateField,'DD/MM/YYYY'))=2020,addYears(date#(dateField,'DD/MM/YYYY'),-10),dateField)

Regards

Re: How to use load script to remove wrong data entry ?

paulyeo11 — Wed, 18 May 2016 07:20:50 GMT

Hi Rud

it work , thank you .

Paul

Re: How to use load script to remove wrong data entry ?

kkkumar82 — Wed, 18 May 2016 07:22:35 GMT

Hi Paul,

Is your problem not to load dates which have 2020 or to replace it with another year

Re: How to use load script to remove wrong data entry ?

paulyeo11 — Wed, 18 May 2016 08:54:36 GMT

Hi Kumar

My issue operator data entry enter wrong date , the date affect my report , and i want to use script to change the year from 2020 to 2010.

Paul

Re: How to use load script to remove wrong data entry ?

paulyeo11 — Wed, 18 May 2016 08:56:34 GMT

Hi Kushal

Do you mean i can do some thing like below :-

Binary "C:\Users\QV\Dropbox\QV file to PY Dropbox\000_qvd_v11.qvw";

where year(DateField)<= year(today());

So those year more then current year will change to current year it is ?

Paul

Re: How to use load script to remove wrong data entry ?

paulyeo11 — Thu, 19 May 2016 14:49:17 GMT

Hi Rudolf

thank you very much for your script . I have tested it work fine. many thank.

Paul