topic Re: Help in logic in QlikView

Help in logic

Wed, 25 Nov 2020 16:16:04 GMT

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 !

Re: Help in logic

Anil_Babu_Samineni — Sun, 27 Nov 2016 08:49:37 GMT

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);

LOAD Country,

Sales,

KeepChar(Sales,'123456789') as Actual_Sales,

Category,

Date

FROM

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

Re: Help in logic

tamilarasu — Sun, 27 Nov 2016 15:28:27 GMT

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:

Re: Help in logic

Sun, 27 Nov 2016 19:10:47 GMT

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

Re: Help in logic

MarcoWedel — Sun, 27 Nov 2016 21:37:44 GMT

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