Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
In my DB I have table with data that need to presented by different groups:
1. | Current |
2. | 1 - 30 |
3. | 31 - 60 |
4. | 61 - 90 |
5. | 91 - 120 |
6. | 121 - 150 |
7. | Over 150 |
For example if we imagine that today is 2/6/1996 and someone paid today he should be loaded as 'Current' ,
If he paid 3 week ago he should be saved in group(1-30), so I need use following calculation:
Group = Current Day- Day of Transaction
I've made Day_ID, don't know the best way how to do this
MEDNO | ACCTNUM | INVOICE | BILL | DATE |
---|---|---|---|---|
28261 | PC | 48612 | 155 | 2/6/1996 |
7010 | NDI | 49251 | 80 | 3/4/1996 |
28595 | NDI | 49300 | 184 | 3/5/1996 |
28623 | NDI | 49375 | 150 | 3/7/1996 |
27457 | NDI | 49390 | 216 | 3/7/1996 |
25325 | NDI | 49392 | 116 | 3/7/1996 |
28716 | NDI | 49551 | 210 | 3/15/1996 |
27091 | NDI | 49567 | 36 | 3/15/1996 |
21412 | NDI | 49568 | 125 | 3/15/1996 |
you could try with class function or interval match function
May be like this:
LET vToday = Num(MakeDate(1996, 2, 6));
Table:
LOAD MEDNO,
ACCTNUM,
INVOICE,
BILL,
DATE,
If(Num(DATE) - $(vToday) < 30, 1,
If(Num(DATE) - $(vToday) < 60, 2,
If(Num(DATE) - $(vToday) < 90, 3,
If(Num(DATE) - $(vToday) < 120, 4,
If(Num(DATE) - $(vToday) < 150, 5, 6))))) as Flag
FROM
[https://community.qlik.com/thread/210465]
(html, codepage is 1252, embedded labels, table is @2);
Using Class in straight table -
Hi,
another solution could be:
using Dual() to get a numerical sortable group field:
table1:
LOAD *,
If(TransactAge<1,Dual('Current',0),If(TransactAge>150,Dual('Over 150',151),Dual(Floor(TransactAge,30,1)&' - '&(Floor(TransactAge,30,1)+29),Floor(TransactAge,30,1)))) as Group;
LOAD *,
DATE-'2/6/1996' as TransactAge
INLINE [
MEDNO, ACCTNUM, INVOICE, BILL, DATE
28261, PC, 48612, 155, 2/6/1996
7010, NDI, 49251, 80, 3/4/1996
28595, NDI, 49300, 184, 3/5/1996
28623, NDI, 49375, 150, 3/7/1996
27457, NDI, 49390, 216, 3/7/1996
25325, NDI, 49392, 116, 3/7/1996
28716, NDI, 49551, 210, 3/15/1996
27091, NDI, 49567, 36, 3/15/1996
21412, NDI, 49568, 125, 3/15/1996
21413, NDI, 49569, 126, 4/15/1996
21414, NDI, 49570, 127, 5/15/1996
21415, NDI, 49571, 128, 6/15/1996
21416, NDI, 49572, 129, 7/15/1996
21417, NDI, 49573, 130, 8/15/1996
21418, NDI, 49574, 131, 9/15/1996
21419, NDI, 49575, 132, 10/15/1996
21420, NDI, 49576, 133, 11/15/1996
21421, NDI, 49577, 134, 12/15/1996
];
hope this helps
regards
Marco