Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
narenjln
Creator
Creator

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

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

25 Replies
narenjln
Creator
Creator
Author

IT throwing the Error Field Name must be Unique

vishsaggi
Champion III
Champion III

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
Champion III
Champion III

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
Creator II
Creator II

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

narenjln
Creator
Creator
Author

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;

narenjln
Creator
Creator
Author

Please see the Attachment

narenjln
Creator
Creator
Author

Hi i have placed my Script

vishsaggi
Champion III
Champion III

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

vishsaggi
Champion III
Champion III

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;

narenjln
Creator
Creator
Author

I have attached the raw data for your reference