Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
vchuprina
New Contributor II

Scale in script

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

MEDNOACCTNUMINVOICEBILLDATE
28261PC486121552/6/1996
7010NDI49251803/4/1996
28595NDI493001843/5/1996
28623NDI493751503/7/1996
27457NDI493902163/7/1996
25325NDI493921163/7/1996
28716NDI495512103/15/1996
27091NDI49567363/15/1996
21412NDI495681253/15/1996
4 Replies

Re: Scale in script

you could try with class function or interval match function

Re: Scale in script

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

Digvijay_Singh
Honored Contributor III

Re: Scale in script

Using Class in straight table -

Capture.JPG

Re: Scale in script

Hi,

another solution could be:

QlikCommunity_Thread_210465_Pic1.JPG

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

Community Browser