Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Changing QVD date Field Values in script

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.

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

subtract 90 years

if(.......,

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

     [Response Due Date]

     ) as [Response Due Date]

View solution in original post

5 Replies
Highlighted
MVP
MVP

subtract 90 years

if(.......,

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

     [Response Due Date]

     ) as [Response Due Date]

View solution in original post

Highlighted
Master II
Master II

Might be as below:

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

Highlighted

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Master II
Master II

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.