topic Re: Inline Table Band Range in QlikView

Inline Table Band Range

saumyashah90 — Fri, 21 Oct 2016 08:41:23 GMT

I have

ID	Date	Amount
ID1	21/10/2016	23242
ID2	25/10/2016	3423
ID3	26/10/2016	234324
ID4	30/10/2016	124334
ID5	01/11/2016	213
ID6	03/11/2016	21321
ID7	04/11/2016	232
ID8	10/11/2016	74632
ID9	15/11/2016	43222

I want to get out put.

Range	Count
0	0
>100 and <1000	2
>1000 and <10000	1
>10000 and <50000	3
>50000	2

Re: Inline Table Band Range

shraddha_g — Fri, 21 Oct 2016 08:53:26 GMT

Try:

Load

if(Amount = 0 ,'0'

if(Amount >100 and Amount < 1000, '>100 and <1000',

if(Amount >1000 and Amount <10000, '>1000 and <10000',

if(Amount >10000 and Amount <50000 ,'>10000 and <50000',

if(Amount >50000,'>50000' ))))) as Range,

Count (ID) as Count

From Table

Group by Amount;

Re: Inline Table Band Range

Aurelien_Martinez — Fri, 21 Oct 2016 09:45:59 GMT

Hi,

data:
LOAD * INLINE [
ID, DATE, AMOUNT
ID1,21/10/2016,23242
ID2,25/10/2016,3423
ID3,26/10/2016,234324
ID4,30/10/2016,124334
ID5,01/11/2016,213
ID6,03/11/2016,21321
ID7,04/11/2016,232
ID8,10/11/2016,74632
ID9,15/11/2016,43222

];

int_:
LOAD * INLINE [
min, max, inter
0, 100, 0
101, 1000, > 100 and <1000
1001, 10000, > 1000 and <10000
10001, 50000, > 10000 and <50000
50001, 99999999999999999999, >50000
];

Join(data)
IntervalMatch(AMOUNT)
LOAD
min,
max
Resident int_
;

Join(data)
LOAD
*
Resident int_
;

DROP Table int_;

Re: Inline Table Band Range

saumyashah90 — Fri, 21 Oct 2016 09:54:46 GMT

Hi People,

I want that solution on UI

.Cant change script at this position

Re: Inline Table Band Range

shraddha_g — Fri, 21 Oct 2016 10:03:00 GMT

Try

Valuelist('0','>100 and <1000', '>1000 and <10000','>10000 and <50000','>50000') in Dimension

In Measure

Pick(Wildmatch(Valuelist('0','>100 and <1000', '>1000 and <10000','>10000 and <50000','>50000'),'0','>100 and <1000', '>1000 and <10000','>10000 and <50000','>50000'),

Count({<Amount = {'0'}>}ID),

Count({<Amount = {">100 <1000"}>}ID),

Count({<Amount = {">1000 <10000"}>}ID),

Count({<Amount = {">10000 <50000"}>}ID),

Count({<Amount = {">50000"}>}ID)

)

Re: Inline Table Band Range

Aurelien_Martinez — Fri, 21 Oct 2016 10:04:28 GMT

You can do a calculated dimension

= if(AMOUNT = 0 ,'0',

if(AMOUNT >100 and AMOUNT < 1000, '>100 and <1000',

if(AMOUNT >1000 and AMOUNT <10000, '>1000 and <10000',

if(AMOUNT >10000 and AMOUNT <50000 ,'>10000 and <50000',

if(AMOUNT >50000,'>50000' )))))

Re: Inline Table Band Range

sunny_talwar — Fri, 21 Oct 2016 10:55:08 GMT

You are looking to do this in the script or the front end?

Re: Inline Table Band Range

saumyashah90 — Tue, 25 Oct 2016 02:57:13 GMT

front end