QV not changing data already loaded into a QVD

[lucasdavis500]

I'm trying to change a small subset of my data....

I have the following code:

IF(Status = 'Archived' AND ISNULL(DateResolved) OR DateResolved = 'NULL', DateResolved = Today(0), DateResolved)

basically, if the "status" is "Archived" and the DateResolved is MISSING, I want the DateResolved to be Today(0), or when the data was loaded, and then it would stay static at that date moving forward.

This is what the data looks like BEFORE and AFTER I run my script..

[swuehl · Accepted solution]

You can't assign a new field record value like this

IF(Status = 'Archived' AND ISNULL(DateResolved) OR DateResolved = 'NULL', DateResolved = Today(0), DateResolved)

In a chart expression, use

IF(Status = 'Archived' AND ISNULL(DateResolved) OR DateResolved = 'NULL', Today(0), DateResolved)

if you want to change the record in your resident table, use something like

LOAD

...

IF(Status = 'Archived' AND ISNULL(DateResolved) OR DateResolved = 'NULL', Today(1), DateResolved) as  DateResolved

...

FROM ...;

If you want to also change it in your data source, then ... you would need to describe a little closer how your setting looks like.

[lucasdavis500]

Unfortunately, I have tried to change this at the Data Source, but it looks like I am going to have to do it within my Application for now. If that status is Archived, I want my QVD's to reflect the day they were loaded.... Hopefully this won't be an issue moving forward....

Thanks for the heads up. I need to retain all the old field record values for ResolvedBy, but just updating the ones where status is "Archived" like I have explained above, and it seems that your method will work, I just need to tweak the syntax a little bit

[lucasdavis500]

Why did you replace Today(0) with Today(1)?

BTW: I do NOT want to do this in a chart expression, unless I want to duplicate my code 100x

[lucasdavis500]

I'm also receiving an error that field names cannot be the exact same, can I not update the same field with (added) values? Do I have to create an entirely new field to do this.....

[swuehl]

If your current script looks like

LOAD

    DateResolved,

    ....

FROM ..,

You can replace the line with following code, which basically is modifying the value when the condition is true, but keeping the original value otherwise.

LOAD

...

IF(Status = 'Archived' AND ISNULL(DateResolved) OR DateResolved = 'NULL', Today(1), DateResolved) as  DateResolved

...

FROM ...;