topic Re: Grouping on A/c using if in QlikView

Grouping on A/c using if

prma7799 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Grouping on A/c using if

devarasu07 — Thu, 28 Dec 2017 07:14:49 GMT

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

Re: Grouping on A/c using if

prma7799 — Thu, 28 Dec 2017 07:17:51 GMT

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

Re: Grouping on A/c using if

pradosh_thakur — Thu, 28 Dec 2017 07:23:39 GMT

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]

Re: Grouping on A/c using if

devarasu07 — Thu, 28 Dec 2017 07:25:03 GMT

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)

Re: Grouping on A/c using if

tresB — Thu, 28 Dec 2017 07:26:17 GMT

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

Load

Account,

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

Re: Grouping on A/c using if

prma7799 — Thu, 28 Dec 2017 07:29:09 GMT

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

'11101000..11102599'

Re: Grouping on A/c using if

pradosh_thakur — Thu, 28 Dec 2017 07:39:03 GMT

please find the sample app

table:

load * inline [

age

];

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;

Re: Grouping on A/c using if

prma7799 — Thu, 28 Dec 2017 07:40:17 GMT

but i want in attached

Re: Grouping on A/c using if

pradosh_thakur — Thu, 28 Dec 2017 07:46:17 GMT

try to replicate this hopefully it will work.

Re: Grouping on A/c using if

tresB — Thu, 28 Dec 2017 09:18:09 GMT

Intervals look uneven in your case, hence class() solution might not be very helpful here. You could try the way using pick(match(.... as Pradosh has suggested above, or maintain a table with upper and lower bounds in a table and use it with intervalmatch().

Re: Grouping on A/c using if

shiveshsingh — Thu, 28 Dec 2017 10:54:27 GMT

Try it using pick(match())

Re: Grouping on A/c using if

prma7799 — Thu, 28 Dec 2017 11:12:07 GMT

Hi Pradosh ,

I have tried your solution but not getting expected output.

PFA..

Thanks

Re: Grouping on A/c using if

pradosh_thakur — Thu, 28 Dec 2017 11:37:58 GMT

pfa

i have done for first few values please make sure you have no ambiguity in the intervals(they should not overlap) and there is wildmatch()+1 is done in the script to counter values which not in any boundry(bucket).

regards

Pradosh

Re: Grouping on A/c using if

MarcoWedel — Thu, 28 Dec 2017 23:45:49 GMT

Hi,

maybe helpful:

Custom Filter(unusual range)

regards

Marco