How to use load script to remove wrong data entry ?

[paulyeo11]

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

[paulyeo11]

Hi All

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

Paul

[Kushal_Chawda]

In your source you can try below condition

LOAD *

FROM Source

Where YearField <= Year(Today());

[Anonymous]

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?

[paulyeo11]

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

[paulyeo11]

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 !

[Kushal_Chawda]

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());

[Anonymous · Accepted solution]

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);

[paulyeo11]

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

[PrashantSangle]

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