Help in logic

[#1]

Hi ,

please help to achieve this logic ..

my requirement is to remove zeros and characters from Sales amount

1)For this i used right function rifgt(sales,6) i got some result but but

2)if my value is more then six digits that value is missing like eg:

Franse  Actual Value                  Expected Value     OutputValue i got

               WER0089637182       89637182               637182 THIS IS WRONG

3)In some cases the values might be 9 digita or 10 or 11 or 3 in this case i want to remove only

zeros and characters from sales amount eg (Uk,canada,German)

Please guide me how to  do this

Thanks !

[Anil_Babu_Samineni]

I Didn't seen any name for Sales Amount

May be this?

LOAD Country,

    Sales,

    PurgeChar(Sales,'abcdefghijklmnopqrstuvwxyz0ABCDEFGHIJKLMNOPQRSTUVWXYZ') as Actual_Sales,

    Category,

    Date

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

OR

LOAD Country,

    Sales,

    KeepChar(Sales,'123456789') as Actual_Sales,

    Category,

    Date

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

[tamilarasu]

Hi John,

I have not seen your application, since I'm using personal edition. Based on the above explanation that you have mentioned, I am providing the solution. It will remove all the characters and only leading zeros.

Load *,
Num(Keepchar(Actual,'0123456789')) as Result;
LOAD * INLINE [
Actual
WER001234567
A12003456789
ABCD00123456
1000000ABCDE
ABCD12000012
];

Result:

[#4]

Thanks a lot for your response...

But in my variable declaration money format is (#,##0.00) means my amount will not more then thousands. means if value in source file is in million we should remove all zeros and keep only last 5 digits

1)But in my source file value more then thousand for eg

  source file:                              Expected value

1) 100000078964                       78964                                                                                                                                     2)  900000065872                     65872                                                                                                                                      3)1000000087534                    87534

[MarcoWedel]

Hi,

one way to only get the numerical value of the last consecutive number block could be:

SALES_COUNTRY:
LOAD Country,
    Sales,
    Num(Mid(Sales,FindOneOf(Sales, PurgeChar(Sales,'-0123456789'), -1)+1)) as Actual_Sales,
    Category,
    Date
INLINE [
    Country, Sales, Category, Date
    USA, F00P00056895, MECH, 24/10/2016
    AFRICA, DOORTE0004329, MECH, 24/10/2016
    JAPAN, SDEORTE00897653, MECH, 24/10/2016
    CHINA, LON00S00000258, MECH, 24/10/2016
    INDIA, POSDEF00075833, MECH, 24/10/2016
    FRANCE, WER0089637182, MECH, 24/10/2016
    CHILE, POLED000000234, MECH, 24/10/2016
    MEXICO, -3789, MECH, 24/10/2016
    CHILE, LWSERDEF000821, MECH, 24/10/2016
    UK, kl897626856, MECH, 24/10/2016
    CANADA, OPU8975316294, MECH, 24/10/2016
    GERMAN, YT98957638936, MECH, 24/10/2016
    ITALY, AB12CD4567890, MECH, 24/10/2016   
];

(though not being sure if this is what you need, because your first and second post are contradictory in the number of required digits.)

hope this helps nevertheless

regards

Marco