Change data values after load

[#1]

I have a table where once loaded I need to change the data values based on certain criteria.

use case: 

• We have Sales that are split between salespeople.   Due to GIGO data entry, the data does not always come in aligned.
• After aligning the fields through Resident loads and rejoining, Some data values are wrong
• When I used to load this data to MySQL, I would run a procedure to fix the data along the lines of this
  • SQL:   UPDATE table SET field=value WHERE field2=0

SalesNo     Salesperson     Position     Percent

100            Dave               Split          0

200            Jim                  Primary     0

300            Mary                Split          100

so my SQL statements would be:

1. UPDATE table SET Position=Primary WHERE Position= Split and Percent=100
2. UPDATE table SET Position=Primary WHERE Position=Split and Percent=0
3. UPDATE table SET Percent=100 WHERE Position = Primary and Percent=0

Not sure how to accompish this in QV

Thanks!

[sunny_talwar · Accepted solution]

I would probably do this in the load script.

Load yourFields,

        Salesperson,

        SalesNo,

        Position,

        Percent,

       If(Position = 'Split' and (Percent = 100 or Percent = 0), 'Primary', Position) as NewPosition,

       If(Position = 'Primary' and Percent = 0, 100, Percent) as NewPercent

Resident yourTable;

Hope this is what you are looking for.

Best,

S

[#3]

Problem is I do not want to create new fields.   The Data has to be corrected in the field it sits as I do calculations and conditional statements based on the value in each field.

Or am I missing something?  I'll give it a shot

[sunny_talwar]

Try this, may be this can get more helpful.

Load yourFields,

        Salesperson,

        SalesNo,

       If(Position = 'Split' and (Percent = 100 or Percent = 0), 'Primary', Position) as Position,

       If(Position = 'Primary' and Percent = 0, 100, Percent) as Percent

Resident yourTable;

The reason I left "original" Position and Percent in place is for you to check if the script is working as you would hope it should work. But you can always use the above script to replace the current field names in the table.

Let me know if this is useful.

Best,

S

[MarcoWedel]

you don't have to create new fields.

So modifying sunindia's example:

Load yourFields,
        Salesperson,
        SalesNo,
      If(Position = 'Split' and (Percent = 100 or Percent = 0), 'Primary', Position) as Position,
      If(Position = 'Primary' and Percent = 0, 100, Percent) as Percent
Resident yourTable;

hope this helps as well

regards

Marco