20 Replies Latest reply: Sep 16, 2014 12:46 AM by jagan mohan rao appala RSS

    Dynamic scripting

      hi Qv,

       

      Temp:

      LOAD Name,

           Date,

           Year(Date) as Year,

           Sales

      FROM

      Data.xlsx

      (ooxml, embedded labels, table is [Dummy Sales]);

       

      Temp1:

      NoConcatenate

      LOAD

      *

      Resident Temp

      Order by Name, Date;

       

      DROP Table Temp;

       

      CumulativeSum:

      NoConcatenate

      LOAD

           Name,

           Date,

           Year,

           Sales,  

           If(Name <> Previous(Name) or Year <> Previous(Year), Sales, RangeSum(Sales, Peek('CumulativeSum'))) AS CumulativeSum    

      Resident Temp1;

       

      DROP Table Temp1;

      ---------------------------------------------------------------------------------

       

      i have done Cumulative sum for above and the problem is i need commission for Different names commision may vary how to give dynamic in set analysic plz help me

       

       

       

      Name

       

       

      Year

       

      Slab

       

      Commission

       

      Peter

       

      2014

       

      $0-$50000

       

      1%

       

      peter

       

      2014

       

      $50001-$100000

       

      1.5%

       

      peter

       

      2014

       

      $100001-$150000

       

      2%

       

      peter

       

      2014

       

      >$150001

       

      2.5%

      john 2014$0-$50000

      1.5%

       

      like this the commision varies plz help

       

      -lohi

        • Re: Dynamic scripting
          jagan mohan rao appala

          Hi,

           

          Can you attach some sample file? 

           

          Try like this

           

          Dimension : Name, Year

          Expression:

          If(Sum(Sales) > 150001, 0.025,

               If(Sum(Sales) >= 100001, 0.02,

               If(Sum(Sales) >= 50001,  0.015,

                If(Sum(Sales) >= 0, 0.01))))

           

          Hope this helps you.

           

          Regards,

          Jagan.

           

            • Re: Dynamic scripting

              hi jagan ,

               

              plz check the above table slap may varies i need dynamic expression ,

               

              -lohi

              • Re: Dynamic scripting
                jagan mohan rao appala

                Hi,

                 

                Try like this in script

                 

                Data:

                LOAD

                *,

                If(Sales > 150001, '>$150001',

                     If(Sales >= 100001, '$100001-$150000',

                     If(Sales >= 50001,  '$50001-$100000',

                      If(Sales >= 0, '$0-$50000')))) AS Slab;

                LOAD

                     Name,

                     Year,

                     Sum(Sales) AS Sales

                FROM DataSource

                Group by Name,  Year;

                 

                LEFT JOIN

                LOAD

                Name,

                Year,

                Slab,

                Commission

                FROM CommissionSource;


                Hope this helps you.


                Regards,

                Jagan.

                  • Re: Dynamic scripting

                    hi jagan ,

                    hope it helps but

                    Peter

                     

                    2014

                     

                    $0-$50000

                     

                    1%

                    john 2014$0-$50000

                    1.5%

                    if this the requirement the

                    peter will get 1%

                    john also get 1%

                    -lohi

                    • Re: Dynamic scripting

                      hi jagan ,

                       

                      i have tried like this

                       

                       

                      Commision:

                      LOAD*,

                         

                          if(CumulativeSum<=50000,'1%',if(CumulativeSum>=50001 and CumulativeSum<=100000,'1.5%',if(CumulativeSum>=100001 and CumulativeSum <=150000,'2%',if(CumulativeSum>=150001,'2.5%','NULL')))) As Comm

                       

                       

                      Resident CumulativeSum

                      Order by Name,Year

                      ;

                      Comm:

                      LOAD * Resident Commision Order by Commision.Name,Commision.Year;

                         

                      Commision1:

                      LOAD     Commision.Name,   

                              Commision.Date,

                              Commision.Year,

                              (Commision.Comm*Commision.CumulativeSum) as Cumm   

                              

                       

                      Resident Commision   

                       

                      Order by Commision.Name,Commision.Year

                      ;

                       

                      but the problem is slap may varies depends on Users.

                       

                      - lohi

                        • Re: Re: Dynamic scripting

                          hi QV's ,

                           

                          Any idea on this dynamic content,

                           

                          -lohi

                            • Re: Dynamic scripting
                              jagan mohan rao appala

                              Hi,

                               

                              Try this script

                               

                              Temp:

                              LOAD Name,

                                   Date,

                                   year(Date) as Year,

                                   Sales

                              FROM

                              [Data (3).xlsx]

                              (ooxml, embedded labels, table is [Dummy Sales]);

                               

                               

                              CumulativeSum:

                              LOAD

                              *,

                              If(CumulativeSum > 150001, '>$150001',

                                   If(CumulativeSum >= 100001, '$100001-$150000',

                                   If(CumulativeSum >= 50001,  '$50001-$100000',

                                    If(CumulativeSum >= 0, '$0-$50000')))) AS Slab;

                              LOAD

                                   Name,

                                   Date,

                                   Year,

                                   Sales,  

                                   If(Name <> Previous(Name) or Year <> Previous(Year), Sales, RangeSum(Sales, Peek('CumulativeSum'))) AS CumulativeSum;

                              LOAD

                              *

                              Resident Temp

                              Order by Name, Date;

                              DROP Table Temp;

                               

                              Left Join(CumulativeSum)

                              LOAD

                              Name, Year, Slab,

                              Num(Num#(Commission, '#.##%'), '#.00') AS Commission

                              INLINE [  

                                  Name, Year, Slab, Commission

                                  Peter, 2014, $0-$50000, 1%

                                  Peter, 2014, $50001-$100000, 1.50%

                                  Peter, 2014, $100001-$150000, 2%

                                  Peter, 2014, >$150001, 2.50%

                                  Tom, 2014, $0-$50000, 1.50%

                              ];

                               

                              Hope this helps you.

                               

                              Regards,

                              Jagan.

                                • Re: Dynamic scripting

                                  hi jagan,

                                   

                                  thxs for reply i have worked on this but the reqmnt is if we have millon names then we cannot create that many in inline right, the point is we have to use interval match fuction between commission and cumulative sum its should loop and check . i dont knw for loop thts why iam strucked

                                   

                                  -lohi

                                    • Re: Dynamic scripting
                                      jagan mohan rao appala

                                      Hi,

                                      I did this in Inline because your excel file has no data, you can change the script to load from your DataSource, I think this is enough no need of IntervalMatch().

                                       

                                      Regards,

                                      Jagan.

                                        • Re: Dynamic scripting

                                          Temp:

                                          LOAD Name,

                                               Date,

                                               year(Date) as Year,

                                               Sales

                                          FROM

                                          [C:\Users\Harish Allam\Desktop\Data.xlsx]

                                          (ooxml, embedded labels, table is [Dummy Sales]);

                                           

                                           

                                           

                                          Temp1:

                                          NoConcatenate

                                          LOAD

                                          *

                                          Resident Temp

                                          Order by Name, Date;

                                           

                                          DROP Table Temp;

                                           

                                           

                                          CumulativeSum:

                                          NoConcatenate

                                          LOAD

                                               Name,

                                               Date,

                                               Year,

                                               Sales,  

                                               If(Name <> Previous(Name) or Year <> Previous(Year), Sales, RangeSum(Sales, Peek('CumulativeSum'))) AS CumulativeSum    

                                          Resident Temp1 Order by Name,Date;

                                           

                                          DROP Table Temp1;

                                           

                                           

                                          dd1:

                                          LOAD * INLINE [

                                              StartR,EndR,Commision

                                              0,50000,1%

                                              50001,100000,2%

                                              100001,150000,3%

                                              150001,200000,4%

                                          ];

                                           

                                           

                                           

                                          IntervalMatch (CumulativeSum) LOAD StartR as Start,EndR Resident dd1 ;

                                           

                                           

                                          here my problem is dynamicly we can change the Cummision if i want to give peter 10% i should do tht , how can i do tht ?

                                           

                                          -lohi

                                            • Re: Dynamic scripting
                                              jagan mohan rao appala

                                              Hi,

                                               

                                              Can you attach your sample Commision data in Excel file?  Your commision data should consists of StartR,EndR, Name, Commision, then only we can do this.

                                               

                                              Regards,

                                              Jagan.

                                                • Re: Re: Dynamic scripting

                                                  hi  jagan ,

                                                   

                                                  Please find attachment

                                                   

                                                  - lohi

                                                  • Re: Dynamic scripting

                                                    hi jagan ,

                                                     

                                                    If data is like this how to do ,

                                                     

                                                    Name            Year             Start             End     Commission
                                                    Peter20140600001.0%
                                                    Peter2014600011000001.5%
                                                    Peter20141000011500002.0%
                                                    Peter20150500001.0%
                                                    Peter2015500011000001.5%
                                                    Peter20151000011500002.0%
                                                    Tom20140500001.2%
                                                    Tom2014500011000001.6%
                                                    Tom20141000011500002.5%
                                                    Tom20150500001.2%
                                                    Tom2015500011000001.6%
                                                    Tom20151000011500002.5%
                                                    Mark20140500000.8%
                                                    Mark201450001800001.4%
                                                    Mark2014800011400002.0%
                                                    Mark20150500000.8%
                                                    Mark201550001800001.4%
                                                    Mark2015800011400002.0%
                                                      • Re: Dynamic scripting
                                                        jagan mohan rao appala

                                                        Hi,

                                                         

                                                        Try Advanced Interval match (Extended Syntax) topic in Qlikview Help file.

                                                         

                                                        Temp:

                                                        LOAD Name,

                                                             Date,

                                                             year(Date) as Year,

                                                             Sales

                                                        FROM

                                                        [C:\Users\Harish Allam\Desktop\Data.xlsx]

                                                        (ooxml, embedded labels, table is [Dummy Sales]);

                                                         

                                                        Temp1:

                                                        NoConcatenate

                                                        LOAD

                                                        *

                                                        Resident Temp

                                                        Order by Name, Date;

                                                         

                                                        DROP Table Temp;

                                                         

                                                        CumulativeSum:

                                                        NoConcatenate

                                                        LOAD

                                                             Name,

                                                             Date,

                                                             Year,

                                                             Sales, 

                                                             If(Name <> Previous(Name) or Year <> Previous(Year), Sales, RangeSum(Sales, Peek('CumulativeSum'))) AS CumulativeSum   

                                                        Resident Temp1 Order by Name,Date;

                                                         

                                                        DROP Table Temp;

                                                         

                                                        LEFT JOIN IntervalMatch (CumulativeSum, Name, Year)

                                                        LOAD

                                                             Start,

                                                             End,

                                                             Name,

                                                             Year

                                                        resident Key;

                                                         

                                                        Regards,

                                                        Jagan.