Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
subtract 90 years
if(.......,
Date(AddYears(Date#([Response Due Date], 'MM/DD/YYYY'), -90), 'MM/DD/YYYY') ,
[Response Due Date]
) as [Response Due Date]
subtract 90 years
if(.......,
Date(AddYears(Date#([Response Due Date], 'MM/DD/YYYY'), -90), 'MM/DD/YYYY') ,
[Response Due Date]
) as [Response Due Date]
Might be as below:
Replace([Response Due Date],'2106','2016') as [Response Due Date]
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
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
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.