Grouping on A/c using if

[prma7799]

Hi All,

I have account number and i want to do grouping(250+) on that account numbers like below

[Grouping A/c]                  A/c

11101000..11102599        .......

11102000..11102499        .......

11103000..11103999        .......

11200000..11299999        .......

I am achieving this using if condition like below

      if (Code>= 11101000 and Code <= 11102599,  '11101000..11102599',

      if (Code>= 11102000 and Code <= 11102499,  '11102000..11102499'

    , '0'    )) as [Grouping A/c]

My problem is that when i run above query for 250+ if statements qv is not allowing

Please suggest in other way

Error in expression:

"Too complex, more than 100 levels nesting"

Please any one can suggest in other way ...

Thanks

[devarasu07]

Hi,

have you tried Pick match method instead basic nested if condition?

also another way, preceeding load suite your case (i.e u can split account grouping upto 90 is load , another 90 as 2nd preceding load etc)

some more reference you can check this thread also

Too complex, more than 100 levels nesting

Thanks

Deva

[prma7799]

You mean to say i need to use all 250+ grouping in multiple preceding load using if with the grouping upto 90.

[pradosh_thakur]

something like this

pick(match(-1,Code>= 11101000 and Code <= 11102599,Code>= 11102000 and Code <= 11102499,......etc .),  '11101000..11102599','11102000..11102499' ...etc) as [Grouping A/c]

[devarasu07]

Hi,

Yes that is one method and it should work. you can try these two method, still if you have issue then share your script we can try to provide the solution. also there one more way using data island / mapping load (here u can define your Grouping as inline load /static file (if this grouping not changing frequently then u can use this way also but best way is method 1 & 2)

[tresesco]

If interval is fixed, try using class(), like:

Load

          Account,

          Class(Account, 2500, '..') as AccountGroup

[prma7799]

If I am using above solution then only one grouping is showing  for

'11101000..11102599'

[pradosh_thakur]

please find the sample app

table:

load * inline [

age

1

2

3

15

17

20

30

39

];

load age,pick(match(-1,age>0 and age <10,age>=10 and age<20,age >= 20 and age <30 , age >=30 and age <40)+1,'no grouping','0-9','10-19','20-29','30-39') as age_band

resident table;

[prma7799]

but i want in attached

[pradosh_thakur]

try to replicate this hopefully it will work.