split range of values

[Anonymous]

Hi Guru's,

I have Account Numbers starting from 100000 to 999999 and corresponding Balances .

I want to show the output as below

Column1   column2  

100000     100001    

                 100002 and till  <200000

200000      200001

                   200002  and Till <300000

how to achieve the requirement,please find the attached data file and out put put format in second tab of the sheet.

Thanks,

Raj

[ogautier62]

Hi,

you can try as dimensions :

floor(num(AccountNumber)/100000)*100000  for col1

AccountNumber for col2

if field is num floor(account ....

regards

[vvira1316]

Hi Rajiv,

Can you please clarify few observation on output tab.

380100	-71,648,162.85	##############
400000	390100	-	-
400100	-	-
400500	-	-

Is 390100 belong 300000 or 4000000?

Also you only have 100000, 200000, 3000000 but not beyond that. Is that how it should be?

[ogautier62]

we are not financial specialist !

you are Vijay maybe it'only under 4000000

so if(accountnumb<4000000,floor(num(AccountNumber)/100000)*100000, accountNumber)

if rule is complex : it's better to create a hierarchy

[Anonymous]

Hi Olivier,

Thanks for your time ,tried with your expression in account number description .its not working as expected.

Thanks,

Raj

[yashdeep]

You can try this in script

LOAD

     div([PARENTA/C'],100000)*100000 as Parent_Acc,

     [PARENTA/C'] as Acc,

     [PTD Balance],

     [YTD Balance]

FROM

[From & 2.xlsx]

(ooxml, embedded labels, table is source);

[Anonymous]

can you post your qlikview app,for me derived field showing as blank.

[yashdeep · Accepted solution]

Please find the qvw