Changing QVD date Field Values in script

[lucasdavis500]

how can I change the date value of a field in my QVD historical file?

I found some anomalies in my data that we're working to resolve, but I don't want to load all the previous tables I have (they are month end tables, and these anomalies occurred awhile ago) and I would like to change their value. Some analysts at my company entered: MM/DD/2106 for the date they were supposed to enter instead of 2016.

Here's what I tried, and it's not working.... any suggestions?

if(Pkey = 33479, [Response Due Date] = date(date#(06/04/2016, 'MM/DD/YYYY')), [Response Due Date])

I also tried:

if(Pkey = 33479, left(date([Response Due Date],'MM/DD/YYYY'),6)&year(today()),[Response Due Date])

The date is not changing though. It's still 06/04/2106 when I view in a table box.

[maxgro · Accepted solution]

subtract 90 years

if(.......,

     Date(AddYears(Date#([Response Due Date], 'MM/DD/YYYY'), -90), 'MM/DD/YYYY') ,

     [Response Due Date]

     ) as [Response Due Date]

[trdandamudi]

Might be as below:

Replace([Response Due Date],'2106','2016') as [Response Due Date]

[Anil_Babu_Samineni]

For first you may missed the Required format

if(Pkey = '33479' and [Response Due Date] = date(date#(MakeDate('06/04/2016'), 'MM/DD/YYYY'),'RequiredDateFormat'), [Response Due Date])

Note: And Finally, I would read this to change before transformation. I mean, please change DB and then again store into Qvd then load to the QlikView.

Then, It showing lot make sense Or else you may get trouble with the punch of Year

[Anil_Babu_Samineni]

What if Duke has same DB starting from 2001 to 2016 same format. Do we write Pick and match to get those. Can you sort it out

[trdandamudi]

Based on his question, I think the date is entered wrong only for the PKey=33479 and he wants to do this change on the script side. We can achieve this by simply using Preceding load like below:

Option I

Data:

Load

Pkey,

IF (Pkey=33479,Replace(Response_Due_Date,2106,2016),Response_Due_Date) as Response_Due_Date;

Load * Inline [

Pkey,Response_Due_Date

33479,6/04/2106

33478,6/04/2016

33476,7/04/2016

] ;

Regardless of the Pkey, if he wants to change the year part from 2106 to 2016 then he can use the below Option II :

Option II

Data:

Load

Pkey,

Replace(Response_Due_Date,2106,2016) as Response_Due_Date;

Load * Inline [

Pkey,Response_Due_Date

33479,6/04/2106

33478,6/04/2016

33476,7/04/2016

];

Note: Make sure you adjust the date formats according to your needs. By just adding the preceding load, it is simple and easy for maintenance rather then troubleshooting whole code.