How to modify values of loaded tables from SQL in Qlikview

[Anonymous]

Hi,

I do not have access to the SQL tables where the data originates from. Is there a way to edit the data in Qlikview itself through the script in order to modify the values.

So for a sample load script below:

LOAD RecordID,

    Data1,

    Data2;

SQL SELECT *

FROM $(DB).dbo.DATASET;

The table looks like this:

RecordID	Data1	Data2
ABC	100	100
DEF	150	350

How do I change the value of say, Data1, for RecordID, ABC, from 100 to 300 and then for DEF from 150 to 500? The desired set of values should be:

RecordID	Data1	Data2
ABC	300	100
DEF	500	350

Thanks in advance!

[tresesco]

Try like:

LOAD

     RecordID,

   If(RecordID ='ABC',300,

          If( RecordID ='DEF',500,Data1)) as Data1,

    Data2;

SQL SELECT *

FROM $(DB).dbo.DATASET;

Hope, this gives you an idea and help modify according to your need.

[Anonymous]

if it is only the two values you can do it as follows

LOAD RecordID,

    if (Data1=100, 300, if Data1=150, 500)) as Data1,

    Data2;

SQL SELECT *

FROM $(DB).dbo.DATASET;

if you load more rows, than it gets complicated if you do not have an algorithm

[Peter_Cammaert]

If the necessary changes simply depend on the value of RecordID, you can use a mapping table.

I would first look for a system in the required changes. If you find one, you can replace the applymap() call with an expression that works for all rows.

Peter