Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am having the Special Character as '-' and i need to replace it with the 'Sample' in qlik View in script level
IT throwing the Error Field Name must be Unique
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;
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.
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
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;
Please see the Attachment
Hi i have placed my Script
Can you paste your Raw data excel sheet before implementing my script?
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;
I have attached the raw data for your reference