topic How to modify values of loaded tables from SQL in Qlikview in QlikView

How to modify values of loaded tables from SQL in Qlikview

Anonymous — Mon, 19 Oct 2015 09:17:01 GMT

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!

Re: How to modify values of loaded tables from SQL in Qlikview

tresB — Mon, 19 Oct 2015 09:22:53 GMT

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.

Re: How to modify values of loaded tables from SQL in Qlikview

Anonymous — Mon, 19 Oct 2015 09:23:01 GMT

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

Re: How to modify values of loaded tables from SQL in Qlikview

Peter_Cammaert — Mon, 19 Oct 2015 09:24:09 GMT

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