Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have an issue like i want to load data on first level in qvd's of a particular column as if the data has VAL as its last three letters then the data should be loaded into qvd without these three letters i.e. it has to be trimmed by three letters from end.
Please help me solving the issue.
Thanks in advance.
Hi,
Try with this example
LOAD Column,PurgeChar(Column,'VAL') as NewColumn;
LOAD * Inline
[
Column
aaaVAL
bbb
ccc
ddddVAL
eeeff
tttVAL
];
Regards
Anand
Thanks for the reply. but i cant do inline load as the data is coming from the database and i cant hard code it. please suggest any other solution or suggest if we can modify this to have required result
Try something like this..
Table1:
Load *,
IF(Right(Filed1,3)='VAL',Left(Filed1,Len(Field1)-3) As Filed1_New;
SQL Select * From Table1;
If I am not wrong, If we use purgechr(), it will delete the characters with in the strings as well.
PurgeChar('VsdAdfL','VAL') gives sddf.
PurgeChar('VsdAdfLVAL','VAL') also give sddf.
Hi Shashank,
Then try this way and do this for your source file in the load script if possible
Source:
LOAD * Inline [
Column
aaaVAL
bbb
ccc
ddddVAL
eeeff
tttVAL ];
New:
LOAD
Column,
if(Right(Column,3)='VAL',mid(Column,1,Len(Column)-3),Column) as NewColumn
Resident Source;
DROP Table Source;
In Front end as a calculated dimension
Otherwise you can use this expression in the front end also it will work completely
if(Right(Column,3)='VAL',mid(Column,1,Len(Column)-3),Column)
Regards
Anand
Hi,
if you load from your database you can make it replace the substring for you directly when you import your data.
You can use the REPLACE function like this:
Table1:
LOAD *;
SQL SELECT
Field1,
Field2,
REPLACE(Field3, 'VAL') as Field3
From Table1;
Giacomo
Hi,
one solution could be:
tabInput:
LOAD * INLINE [
Field
something1VAL
something2
something3Val
something4val
something5
something6va
valsomething7
vasomething8
vlsomething9
valsomevalthing10VAL
valsomeVALthing11
VALsomething12
];
NoConcatenate
tabOutput:
LOAD If(Field like '*VAL', Left(Field,Len(Field)-3), Field) as Field
Resident tabInput;
DROP Table tabInput;
hope this helps
regards
Marco