How to recode none sequences number ?

[paulyeo11]

Hi All

I have a field name call [reporting code]

it have number running start from 1 to 999

My script is below working fine :-

If([Reporting Code]>=100 and [Reporting Code]<=119,'1 PAYROLL',

If([Reporting Code]>=102 and [Reporting Code]<=102,'1 PAYROLL',

If([Reporting Code]>=103 and [Reporting Code]<=103,'1 PAYROLL',

If([Reporting Code]>=104 and [Reporting Code]<=104,'1 PAYROLL',

If([Reporting Code]>=105 and [Reporting Code]<=105,'1 PAYROLL',

If([Reporting Code]>=108 and [Reporting Code]<=108,'1 PAYROLL',

If([Reporting Code]>=120 and [Reporting Code]<=127,'2 TRANSPORT ALLOWANCE',

If([Reporting Code]>=150 and [Reporting Code]<=159,'3 PROFESSIONAL CHARGES',

If([Reporting Code]>=175 and [Reporting Code]<=175,'11 EXCHANGE LOST',

If([Reporting Code]>=180 and [Reporting Code]<=183,'7 INTEREST',

If([Reporting Code]>=191 and [Reporting Code]<=191,'12 OFFICE RENTAL',

If([Reporting Code]>=196 and [Reporting Code]<=196,'13 WRITE OFF',

If([Reporting Code]>=200 and [Reporting Code]<=220,'5 CAR & DEMO EXP',

If([Reporting Code]>=221 and [Reporting Code]<=319,'1 PAYROLL',

If([Reporting Code]>=205 and [Reporting Code]<=205,'7 INTEREST',

If([Reporting Code]>=320 and [Reporting Code]<=330,'6 STAFF WELFARE',

If([Reporting Code]>=331 and [Reporting Code]<=349,'8 TRANSPORT ALLOWANCE',

If([Reporting Code]>=350 and [Reporting Code]<=359,'9 DELIEVERY & PROMOTION',

If([Reporting Code]>=360 and [Reporting Code]<=374,'10 ENTERTAINMENT & FRIEGHT',

))))))))))))))))))) as [NAME_EXP],

My question is those code out off above i need to recode them into OTHER EXPENSE.

I Try to add one more line at last line , it does not work.

if ([Reporting Code]='','','OTHER EXPENSE'

Paul

[alexandros17]

Try this:

....

'FRIEGHT','OTHER EXPENSE'

so add 'OTHER EXPENSE' following 'FRIEGHT', it must work!!!

[paulyeo11]

Hi Sir

I unable to understand  what you try to siad , and i unable to apply your advise on to my QV Doc , Hope you can take a look of my Doc.

My reporting code number from 1 to 999 is belong to Expense. and i have already recode part of it , and i need to make the rest as OTHER expense.

Paul

[alexandros17]

Hi,

replace your code with this one and let me know ...

load

[Reporting Code],



If([Reporting Code]>=100 and [Reporting Code]<=119,'1 PAYROLL',

If([Reporting Code]>=102 and [Reporting Code]<=102,'1 PAYROLL',

If([Reporting Code]>=103 and [Reporting Code]<=103,'1 PAYROLL',

If([Reporting Code]>=104 and [Reporting Code]<=104,'1 PAYROLL',

If([Reporting Code]>=105 and [Reporting Code]<=105,'1 PAYROLL',

If([Reporting Code]>=108 and [Reporting Code]<=108,'1 PAYROLL',



If([Reporting Code]>=120 and [Reporting Code]<=127,'2 TRANSPORT ALLOWANCE',

If([Reporting Code]>=150 and [Reporting Code]<=159,'3 PROFESSIONAL CHARGES',

If([Reporting Code]>=175 and [Reporting Code]<=175,'11 EXCHANGE LOST',

If([Reporting Code]>=180 and [Reporting Code]<=183,'7 INTEREST',

If([Reporting Code]>=191 and [Reporting Code]<=191,'12 OFFICE RENTAL',

If([Reporting Code]>=196 and [Reporting Code]<=196,'13 WRITE OFF',



If([Reporting Code]>=200 and [Reporting Code]<=220,'5 CAR & DEMO EXP',

If([Reporting Code]>=221 and [Reporting Code]<=319,'1 PAYROLL',

If([Reporting Code]>=205 and [Reporting Code]<=205,'7 INTEREST',



If([Reporting Code]>=320 and [Reporting Code]<=330,'6 STAFF WELFARE',

If([Reporting Code]>=331 and [Reporting Code]<=349,'8 TRANSPORT ALLOWANCE',

If([Reporting Code]>=350 and [Reporting Code]<=359,'9 DELIEVERY & PROMOTION',

If([Reporting Code]>=360 and [Reporting Code]<=374,'10 ENTERTAINMENT & FRIEGHT','OTHER EXPENSE'

))))))))))))))))))) as [NAME_EXP]

[paulyeo11]

Hi Alex

You are very smart. I never expect this is possible , it work.

I have one more question , can you add some more code in the expression , so that it will classify those number which from 1 to 999 as Expense , as now your above code include those number which 1 to 999 till 800000 as Other expense.

if not possible , then i will need to use button to recode . which not so smart.

Paul

[alexandros17 · Accepted solution]

I Hope this is what you need

load

[Reporting Code],

If([Reporting Code]>=100 and [Reporting Code]<=119,'1 PAYROLL',

If([Reporting Code]>=102 and [Reporting Code]<=102,'1 PAYROLL',

If([Reporting Code]>=103 and [Reporting Code]<=103,'1 PAYROLL',

If([Reporting Code]>=104 and [Reporting Code]<=104,'1 PAYROLL',

If([Reporting Code]>=105 and [Reporting Code]<=105,'1 PAYROLL',

If([Reporting Code]>=108 and [Reporting Code]<=108,'1 PAYROLL',

If([Reporting Code]>=120 and [Reporting Code]<=127,'2 TRANSPORT ALLOWANCE',

If([Reporting Code]>=150 and [Reporting Code]<=159,'3 PROFESSIONAL CHARGES',

If([Reporting Code]>=175 and [Reporting Code]<=175,'11 EXCHANGE LOST',

If([Reporting Code]>=180 and [Reporting Code]<=183,'7 INTEREST',

If([Reporting Code]>=191 and [Reporting Code]<=191,'12 OFFICE RENTAL',

If([Reporting Code]>=196 and [Reporting Code]<=196,'13 WRITE OFF',

If([Reporting Code]>=200 and [Reporting Code]<=220,'5 CAR & DEMO EXP',

If([Reporting Code]>=221 and [Reporting Code]<=319,'1 PAYROLL',

If([Reporting Code]>=205 and [Reporting Code]<=205,'7 INTEREST',

If([Reporting Code]>=320 and [Reporting Code]<=330,'6 STAFF WELFARE',

If([Reporting Code]>=331 and [Reporting Code]<=349,'8 TRANSPORT ALLOWANCE',

If([Reporting Code]>=350 and [Reporting Code]<=359,'9 DELIEVERY & PROMOTION',

If([Reporting Code]>=360 and [Reporting Code]<=374,'10 ENTERTAINMENT & FRIEGHT',

If([Reporting Code]>=375 and [Reporting Code]<=999,'UNDER 999 OTHER EXPENSE', 'OTHER EXPENSE'

)))))))))))))))))))) as [NAME_EXP]

[paulyeo11]

Hi Alex

Your code work fine.

I change your offer code :-

If([Reporting Code]>=375 and [Reporting Code]<=999,'UNDER 999 OTHER EXPENSE','OTHER EXPENSE'

If([Reporting Code]>=102 and [Reporting Code]<=355,'12 UNDER 355 OTHER EXPENSE'

Because exp Reporting Code start from 103 till 355.

May i ask you why it know how to filter Reporting Code 102 till 355 as 12 UNDER OTHER EXPENSE ? INSTEAD IT very smart only pick up those number which is not include in other if statement.

I just cannot understand why , it so smart... care to share with me why ?

Paul