topic Re: How to replace values in a column in QlikView

How to replace values in a column

Wed, 18 Jun 2014 14:08:18 GMT

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?

Re: How to replace values in a column

its_anandrjs — Wed, 18 Jun 2014 14:11:37 GMT

Try with

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

Eg:-

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

Load * inline

[

Name

ab201406002de

];

OutPut:-

ab2011400101de

Re: How to replace values in a column

Clever_Anjos — Wed, 18 Jun 2014 14:11:49 GMT

LOAD

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

otherfields

from yourqvd.qvd(qvd);

store t into yourqvd.qvd(qvd);

Re: How to replace values in a column

Anonymous — Wed, 18 Jun 2014 14:22:36 GMT

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];

Re: How to replace values in a column

Wed, 18 Jun 2014 14:46:03 GMT

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?

Re: How to replace values in a column

Anonymous — Wed, 18 Jun 2014 14:48:36 GMT

Use the replace function instead of if statement.

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

Re: How to replace values in a column

Clever_Anjos — Wed, 18 Jun 2014 14:52:16 GMT

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

Re: How to replace values in a column

its_anandrjs — Wed, 18 Jun 2014 14:54:49 GMT

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;

Re: How to replace values in a column

Wed, 18 Jun 2014 16:40:03 GMT

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

Re: How to replace values in a column

Wed, 18 Jun 2014 17:11:18 GMT

This did the trick Karthi. Thanks!