Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Community,
I need help/guidance on how to create tiers in QlikView.
I need to create tiers based on count and balance:
Example:
Tiers Counts
0 - 100 = 123
101 - 500 = 456
501 - 5000 = 789
and so on...
Regards,
use this in ur script
Load
...
class(yourfield,50) as yourfield,
from table
Hi Anwar,
Need some more detailed information to guide you with expected output.
use interval match function ...if you have a fixed interval than class is the better option otherwise intervalmatch is the best
Anwar,
As suggested by Gowtham you can use class() function at front end.
Also you can use multiple IF statments at script level.
Something like this:
If(count(Tier)>=0 & count(Tier)<=100, '0-100',
If(count(Tier)>100 & count(Tier)<=500, '100-500',
and so on...
If you can post some sample data to get desired output, then always welcome.
Hope this will help!!
Below is the query that I am trying to run in QlikView. It works perfectly fine in SQL, but it doesn't work in QV.
_________________________
ABC_TABLE:
SQL
SELECT Grid, COUNT([CUSTOMER ID]) FROM
(SELECT [CUSTOMER ID],AMOUNT_LCY, 'GRID' =
CASE
WHEN AMOUNT_LCY< 0 THEN 'DEBIT BALANCE'
WHEN AMOUNT_LCY>= 1 AND AMOUNT_LCY<= 100 THEN '1-100'
WHEN AMOUNT_LCY> 100 AND AMOUNT_LCY<= 500 THEN '100-500'
WHEN AMOUNT_LCY> 500 AND AMOUNT_LCY<= 1000 THEN '500-1000'
WHEN AMOUNT_LCY> 1000 AND AMOUNT_LCY<= 5000 THEN '1000-5000'
WHEN AMOUNT_LCY> 5000 AND AMOUNT_LCY<= 10000 THEN '5000-10000'
WHEN AMOUNT_LCY> 10000 AND AMOUNT_LCY<= 25000 THEN '10000-25000'
WHEN AMOUNT_LCY> 25000 AND AMOUNT_LCY<= 100000 THEN '25000-100000'
WHEN AMOUNT_LCY> 100000 AND AMOUNT_LCY<= 500000 THEN '100000-500000'
WHEN AMOUNT_LCY> 500000 AND AMOUNT_LCY<= 1000000 THEN '500000-1000000'
WHEN AMOUNT_LCY> 1000000 AND AMOUNT_LCY<= 2000000 THEN '1000000-2000000'
WHEN AMOUNT_LCY> 2000000 AND AMOUNT_LCY<= 3000000 THEN '2000000-3000000'
WHEN AMOUNT_LCY> 3000000 THEN 'ABOVE-3000000'
ELSE 'Zero'
END
FROM BALANCE_TABLE
where CATEGORY in ('1001','1003','1006','1512','1517','1951','1960','3036','3193','3194','3195','3911','17278','17279','17288','17289','17574',
'1501','1507','4002','4003','4009','6001','6002','6003','6004','6005','6006','6007','6008','6009','6011','6012','6013','6019','6020',
'6024','17567','1015','1953','21001','21003','21004','21005','21006','21008','21009','21010','21011','21012','21014','21015')
AND update_Date = (select distinct MAX (UPDATE_DATE)
from BALANCE_TABLE)) AS DATA
GROUP BY Grid;
Store ABC_TABLE into ABC_TABLE.qvd;
Drop table ABC_TABLE;
____________________________
What error you are getting during the execution in QV??
Anwar,
I wud suggest you to do it at QV script level/UI level because their are so many inner query are running and also you are creating a bucket at connection level which might be causing this error. May be you are facing server timeout kind of stuff.
So, better do it at QV level.
Balraj,
What would be the procedure at QV level?