topic Re: Scale in script in QlikView

Scale in script

vchuprina — Tue, 22 Mar 2016 13:19:54 GMT

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

Re: Scale in script

avinashelite — Tue, 22 Mar 2016 13:27:57 GMT

you could try with class function or interval match function

Re: Scale in script

sunny_talwar — Tue, 22 Mar 2016 13:42:09 GMT

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

Re: Scale in script

Digvijay_Singh — Tue, 22 Mar 2016 14:00:10 GMT

Using Class in straight table -

Re: Scale in script

MarcoWedel — Sat, 26 Mar 2016 23:58:45 GMT

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