4 Replies Latest reply: Mar 26, 2016 7:58 PM by Marco Wedel RSS

    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
        • Re: Scale in script
          Avinash R

          you could try with class function or interval match function

          • Re: Scale in script
            Sunny Talwar

            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

              Using Class in straight table -

              Capture.JPG

              • Re: Scale in script
                Marco Wedel

                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