topic Re: split range of values in QlikView

split range of values

Anonymous — Tue, 29 May 2018 14:34:33 GMT

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

Re: split range of values

ogautier62 — Tue, 29 May 2018 14:43:22 GMT

Hi,

you can try as dimensions :

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

AccountNumber for col2

if field is num floor(account ....

regards

Re: split range of values

vvira1316 — Tue, 29 May 2018 14:56:28 GMT

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?

Re: split range of values

ogautier62 — Tue, 29 May 2018 15:07:40 GMT

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

Re: split range of values

Anonymous — Wed, 30 May 2018 10:06:22 GMT

Hi Olivier,

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

Thanks,

Raj

Re: split range of values

yashdeep — Wed, 30 May 2018 10:38:12 GMT

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);

Re: split range of values

Anonymous — Wed, 30 May 2018 10:59:33 GMT

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

Re: split range of values

yashdeep — Wed, 30 May 2018 12:36:21 GMT

Please find the qvw