Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
paulyeo11
Valued Contributor II

How to use load script to remove wrong data entry ?

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

1 Solution

Accepted Solutions
hrlinder
Honored Contributor

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

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

14 Replies
paulyeo11
Valued Contributor II

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

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 ?

In your source you can try below condition

LOAD *

FROM Source

Where YearField <= Year(Today());

hrlinder
Honored Contributor

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

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
Valued Contributor II

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

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
Valued Contributor II

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

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 D: and copy to folder AUTO_

Paul !

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

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

hrlinder
Honored Contributor

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

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
Valued Contributor II

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

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 ?

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

Great dreamer's dreams never fulfilled, they are always transcended.
Community Browser