Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
Hi All
I just notice that i typing wrong , what i mean is replace 2020 to 2010 for year.
Paul
In your source you can try below condition
LOAD *
FROM Source
Where YearField <= Year(Today());
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?
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
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 !
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());
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);
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
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