How to replace values in a column

[#1]

Looking to replace the values in a column from a qvd file the file has 3 million rows. Column name is date. I'd like to replace the values 201406002 with 2011400101 for the entire column. Can someone offer a solution?

[its_anandrjs]

Try with

Replace(YourFieldNameHere,'201406002','2011400101')

Eg:-

Load Name, replace(Name,'201406002','2011400101') as NewName;

Load * inline

[

Name

ab201406002de

];

OutPut:-

ab2011400101de

[Clever_Anjos]

t:

LOAD

if(Date=201406002,2011400101,Date) as Date,

otherfields

from yourqvd.qvd(qvd);

store t into yourqvd.qvd(qvd);

[Anonymous · Accepted solution]

Hi,

If u want to replace all the 3 Million rows column value:

Then u can try with this solution:

Test:

Load *,

'2011400101' as Date;

Load field1,field2,.....fieldn  /*Except date field load all the fields here*/

from [Tablename];

[#5]

Thanks clever. Doesnt seem to work though because date comes in as a string and I need to convert it to a date. When I run it errors out as <Date> not found

LOAD

if(date_day_sk='20140602','20140101') as Date,

user_sk,

user_sk as [Event.User],
date_day_sk,
  Date#(Date,'YYYYMMDD') as [Event Date],

  time_sk,
  event_type_sk,
FROM
fact_20140101.qvd
(qvd);

Any hints on how to work around this?

[Anonymous]

Use the replace function instead of if statement.

replace(date_day_sk,'20140602','20140101') as Date

[Clever_Anjos]

If its a string, it´s better use replace instead

[its_anandrjs]

Try this way and remove the single quotes.

tmpTable:

LOAD

if(date_day_sk=20140602,20140101) as Date,

Replace(date_day_sk,20140602,20140101) as CheckDate,

user_sk,

user_sk as [Event.User],
date_day_sk,
//Date#(Date,'YYYYMMDD') as [Event Date], //It is not working because Date field is not in the table try in the resident table

time_sk,
event_type_sk,
FROM
fact_20140101.qvd
(qvd);

FinaleTable:

Noconcatenate

Load *,

Date#(Date,'YYYYMMDD') as [Event Date],

Date#(Date,'YYYYMMDD') as CheckDate

Resident tmpTable;

Drop Table tmpTable;

[#9]

After storing data in suppose xyz.qvd

LOAD [inv turnover],

     if(Date='201406002','2011400101',Date) as Date,

     [Store Value]

FROM

[xyz.qvd]

(qvd);

I hope it will work

[#10]

This did the trick Karthi. Thanks!