topic Re: Change data values after load in QlikView

Change data values after load

Tue, 27 Jan 2015 21:44:35 GMT

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:

UPDATE table SET Position=Primary WHERE Position= Split and Percent=100
UPDATE table SET Position=Primary WHERE Position=Split and Percent=0
UPDATE table SET Percent=100 WHERE Position = Primary and Percent=0

Not sure how to accompish this in QV

Thanks!

Re: Change data values after load

sunny_talwar — Tue, 27 Jan 2015 22:02:05 GMT

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,

Re: Change data values after load

Tue, 27 Jan 2015 23:43:12 GMT

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

Re: Change data values after load

sunny_talwar — Wed, 28 Jan 2015 00:23:03 GMT

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,

Re: Change data values after load

MarcoWedel — Wed, 28 Jan 2015 00:24:20 GMT

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