If statement within Script

Fri, 27 May 2016 14:32:33 GMT

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

Re: If statement within Script

sunny_talwar — Fri, 27 May 2016 16:00:08 GMT

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

Re: If statement within Script

settu_periasamy — Fri, 27 May 2016 19:47:42 GMT

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;

Re: If statement within Script

Thu, 02 Jun 2016 16:47:18 GMT

Creating a mapping table worked. Thanks for your help!

topic Re: If statement within Script in QlikView

If statement within Script

Re: If statement within Script

Re: If statement within Script

Re: If statement within Script