I am having the Special Character as '-' and i need to replace it with the word in qlik View

[Anonymous]

I am having the Special Character as '-' and i need to replace it with the 'Sample' in qlik View in script level

[Anonymous]

IT throwing the Error Field Name must be Unique

[vishsaggi]

May be try this example and change your field accordingly?

Table1:

LOAD RowNo() AS RowNum, Extension INLINE [

Extension

-

201

202

222

200

-

-

-

-

];

SET vRowCount = '=Count(RowNum)';

NoConcatenate

Final:

LOAD RowNum, IF(Extension = '-' AND RowNum = 1, 'No Ext', Extension) AS Extension

Resident Table1;

DROP Table Table1;

[vishsaggi]

Can you paste your full script here you must be using this way ?

LOAD yourfieldname1,

           Yourfieldname2,

           Extension,

           Replace(Extension, '-', 'No Ext') AS Extension

FROM Yoursource;

This will give you error. So do not use the fieldname twice.

[suryaa30]

is '-' a string in data or a null value representation??

Null value

use if(len(trim(Field))<0,'no ext',Field) as Field

String:

use if(trim(Field)='-','no ext',Field) as Field

[Anonymous]

After Applying your Script:

Script Entered:

EMP3:

LOAD * ,[First Name]&' '& [Last Name] as [Full Name],

YearSalary+Bonus as [Total Salary]

Resident EMPi

where Title like 'Account Manager';

DROP Table EMPi;

NoConcatenate

New1:

LOAD RowNo() AS RowNum, Extension INLINE [

Extension

-

104

303

402

403

-

-

-

-

];

Set VRowcount= '=Count(RowNum)';

noconcatenate

New2:

load  RowNum, IF(Extension = '-' AND RowNum = 1, 'No Ext', Extension) AS Extension

Resident New1;

DROP Table New1;

[Anonymous]

Please see the Attachment

[Anonymous]

Hi i have placed my Script

[vishsaggi]

Can you paste your Raw data excel sheet before implementing my script?

[vishsaggi]

From the excel sheet you attached i removed RowNum column and created a new Field in QV script. So in your excel sheet remove RowNum column and then use below script.

Table1:

LOAD EmpID,

     [Last Name],

     [First Name],

     Title,

     HireDate,

     Office,

     Extension,

     [Reports To],

     YearSalary,

     Bonus,

     [Full Name],

     [Total Salary],

     RowNo() AS RowNum

FROM

[..\..\Downloads\TB02_20170908_015802.xls]

(biff, embedded labels, table is Sheet1$, filters(

Remove(Row, Pos(Top, 17))

));

SET vRowCount = '=Count(RowNum)';

NoConcatenate

Final:

LOAD EmpID,

     [Last Name],

     [First Name],

     Title,

     HireDate,

     Office,

     RowNum,

     IF(Extension = '-' AND RowNum = 1, 'No Ext', Extension) AS Extension,

     [Reports To],

     YearSalary,

     Bonus,

     [Full Name],

     [Total Salary]

Resident Table1;

DROP Table Table1;

[Anonymous]

I have attached the raw data for your reference