Trimming of data with where condition

[shanky1907]

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.

[its_anandrjs]

Hi,

Try with this example

LOAD Column,PurgeChar(Column,'VAL') as NewColumn;

LOAD * Inline

[

Column

aaaVAL

bbb

ccc

ddddVAL

eeeff

tttVAL

];

Regards

Anand

[shanky1907]

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

[PradeepReddy]

Try something like this..

Table1:

Load *,

IF(Right(Filed1,3)='VAL',Left(Filed1,Len(Field1)-3) As Filed1_New;

SQL Select * From Table1;

[PradeepReddy]

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.

[its_anandrjs]

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

[giacomom]

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

[MarcoWedel]

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