If statement within Script

[#1]

I am classifying my GL codes into buckets. The trouble I am having is if the same GL code is associated with different buckets. The If statement below is what I have been working with. As you see Total Sales and Revenue both have 5000 accounts associated with them. As it sits right now Total Sales is only coming up in my list table and Revenue is not coming at all. Any help with this or if there is a better way to do this it would be much appreciated.

If(Match([G/L account number - GCGL#],'7100','7200','260','7110','7140','7120','7130','7400','7170','7240','7160','7150'),'Maintenance Expense',

If(Match([G/L account number - GCGL#],'4300','4350','4610','4620','4660','4670','4680','4710','4720','4730','4740','4750','4760','4770','4780','4790','4800','4820','4950', '5000','5010','5020','5200','5210','5220','5300','5310','5400','5410','5420'),'Total Sales',

If(Match([G/L account number - GCGL#],'5000','5200','5300','5400','5010','5210','5310','5410','5420','5020','5220'),'Revenue',

If(Match([G/L account number - GCGL#],'440','450','490','7340','7290','7350','7360','7300','7320','7330','7310','7370'),'Fixed Expense',

If(Match([G/L account number - GCGL#],'60','70','210','220','200','230','250','270','290','300','320','310','510','600','620','660','680','681','690','700','710','720','730','740','750','760','880','890','900','910','920') And Match([G/L account number prefix - GCGL#A],'780','100'),'Overheads Admin',

If(Match([G/L account number - GCGL#],'60','70','210','220','230','240','250','270','280','290','300','320','310','330','510','670','680','681','690','700','730','740','750','760','900','730','910','920')and [G/L account number prefix - GCGL#A]='770','Overheads Maintenance',

If(Match([G/L account number - GCGL#],'10','20','30','40','60','70','210','220','230','250','270','290','300','320','310','510','670','680','681','690','700','730','740','750','760','900','730','910')And [G/L account number prefix - GCGL#A]='760','Overheads Sales',

If([G/L account number prefix - GCGL#A] ='770' and [G/L account number - GCGL#] = '50','Overheads Sales',

If([G/L account number prefix - GCGL#A] ='770' and [G/L account number - GCGL#] = '600','Overheads Admin',

If(Match([G/L account number - GCGL#],'610') And Match([G/L account number prefix - GCGL#A],'760','780','100','790'),'Overheads Admin',

If(Match([G/L account number - GCGL#],'680') And Match([G/L account number prefix - GCGL#A],'780','100','790'),'Overheads Admin',

If([G/L account number - GCGL#]='650','Overheads Sales',

If([G/L account number prefix - GCGL#A] ='100' and [G/L account number - GCGL#] = '200','Overheads Admin',''))))))))))))) as Catergories

[sunny_talwar]

I think you might need to change the data structure, would you be able to provide some sample data with expected result?

[settu_periasamy · Accepted solution]

May be you can try with MAP and ApplyMAP. (the code will less)

Create a Source (may be excel) , put all your account numbers, Prefix and Categories. (should be 2 columns only) to need to load as Mapping prefix.

Sample script

MAP:
Mapping
LOAD [G/L account number - GCGL#] as Account_Number,
Catergories
FROM
Source; // Inline or Excel

  T1:
LOAD [G/L account number - GCGL#],
[G/L account number prefix - GCGL#A],
ApplyMap('MAP','[G/L account number - GCGL#]',ApplyMap('MAP','[G/L account number prefix - GCGL#A]','Unknown')) as Catergories
FROM
Source;

[#4]

Creating a mapping table worked. Thanks for your help!