topic Scanning a data table in QlikView

Scanning a data table

Mon, 10 May 2010 13:39:55 GMT

Hi Guys,

Is it possible to scan and replace values in a table?

Something like this...

load * from table

for each record

replace record.field with 123

end for

Thanks

DerekJ

Scanning a data table

Mon, 10 May 2010 14:00:05 GMT

Not quite sure what you mean - are you just trying to set a specific value to a field regardless of its actual value? if so this will do it

Load fielda,

fieldb,

123 as fieldc

from...

If you want to replace various values with others, take a look at 'map'

Regards,

Gordon

Scanning a data table

Oleg_Troyansky — Mon, 10 May 2010 14:01:08 GMT

If you simply reload the table using resident load, and use any expression you want for the target field. If the table existed before, you'll need to reload it into a different name, then drop the "old" table, then rename from new to old, to preserve the original name. Something like this:

NewTable:

load A, B. '123' as C resident OldTable;

drop table OldTable;

rename table NewTable to OldTable;

Scanning a data table

Mon, 10 May 2010 14:54:48 GMT

Hi Guys,

Thanks for the quick replies. I'd like to do something like this......

If I have a simple table like this

MyTable:-

Field1

I can :-

SQL

FROM MyTable;

This gives

SELECT field1, 0.0 as field2

Field1 Field2

1 0.0

2 0.0

3 0.0

Then, in memory, I want to change the table to look like this

Field1 Field2

1 2

2 4

3 6

where the value of Field2 comes from some calculation that can't be done in the SQL Select.

Thanks,

DerekJ

Scanning a data table

Mon, 10 May 2010 15:07:05 GMT

Hi Darek,

I think I have a solution for your post. here you should use Precedent Load. to get more info on it go to reference manual.

Load Field1,

sum(Field2) as Field 2; //some calculation you want to make here

SQL Select Field1,

0.0 as Field2

from xyz;

The first load without any from statement is the Precedent load. Hope this helps you....

Thanks Joseph......

Scanning a data table

markmccoid — Mon, 12 Jul 2010 17:22:59 GMT

Hi Oleg,

When I try the above both of my tables disappear?!

Here is the code I'm using:

NewProductTable:
Load
ADPRODUCT_ID,
PlacementName,
SuperProduct_ID,
AdSubType,
AdTypeCombo,
Edition,
If(IsNull(BreakoutProduct), ProductName,BreakoutProduct) as ProductName,
RESIDENT AdProduct;

drop table AdProduct;

Rename table NewProductTable to AdProduct;

Is there anything that I could be doing wrong that would cause this?

Thanks

Scanning a data table

markmccoid — Mon, 12 Jul 2010 17:39:31 GMT

I think I've found the answer. All the fields in the new table are the same name. If I add a new dummy field it works.

Thanks,