8 Replies Latest reply: Sep 24, 2014 11:36 AM by Marco Wedel RSS

    Scripting Problem

      Hi everyone,

       

       

            I have Cumulative Sum field.I need last Value which is less than 1 lakh.After 1 lakh ,the data should be

      (1 lakh)-Cumulativesum.Pls help me

       

      Any suggestions would be greatly Appreciated.Little urgent.

       

      Sample data and Application .

       

      PFA,

       

       

      Regards,

      Divya

        • Re: Scripting Problem

          hi ,

            I am using  Below Script

          Tab3:

          Load *,if(CumulativeSum<10000 ,Peek('CumulativeSum',0,'Tab1'))as New_C_sum Resident Tab1;

           

          I am getting last peek value Which is lessthan 1 lakh .I want to replace that value with 1 lakh .Pls suggest me

           

          Regards,

          Divya

            • Re: Scripting Problem
              jagan mohan rao appala

              Hi,

               

              Try this script

               

              Temp:

              LOAD Name, 

                  Date, 

                  Year, 

                  Sales, 

                  CumulativeSum, 

                  Commission ,

                 If(Name = Previous(Name), Mod(CumulativeSum, 100000), CumulativeSum) AS NewCumulativeSum

              FROM [http://community.qlik.com/servlet/JiveServlet/download/614780-127809/Requirement.xls] (biff, embedded labels, table is Sheet1$);

               

              Regards,

              Jagan.

              • Re: Scripting Problem

                Hi Putturudivya,

                 

                Try this. This can replace the last peek value with 1 lakh.

                 

                Temp:

                LOAD
                     RowNo() AS ID,
                     Name,
                     Date,
                     Year,
                     Name&Year AS Name_Year,
                     Sales,
                     CumulativeSum,
                     Commission
                FROM
                [Requirement.xls]
                (
                txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

                MapCumulativeSum:
                MAPPING
                LOAD
                     ID,
                     Peek(Sales,ID) AS Sales
                Resident Temp;

                MapNameYear:
                MAPPING
                LOAD
                     ID,
                     Peek(Name_Year,ID) AS Name_Year
                Resident Temp;

                NoConcatenate
                Fact:
                LOAD
                 

                     *,

                     IF(Name_Year = ApplyMap('MapNameYear',ID) AND CumulativeSum <= 100000 AND CumulativeSum + ApplyMap('MapCumulativeSum',ID) > 100000, 100000, Mod(CumulativeSum,100000)) AS NewCumulativeSum

                Resident Temp;

                DROP Table Temp;

                 

                Linda

              • Re: Scripting Problem
                Marco Wedel

                Hi,

                 

                one possible solution could be:

                 

                QlikCommunity_Thread_134258_Pic1.JPG.jpg

                 

                table1:
                LOAD Name,
                    Date,
                    Year,
                    Sales,
                    CumulativeSum,
                    Commission
                FROM [http://community.qlik.com/servlet/JiveServlet/download/614780-127809/Requirement.xls] (biff, embedded labels, table is Sheet1$);
                
                Left Join (table1)
                LOAD *,
                    If(Name=Previous(Name) and CumulativeSum<=100000 and Previous(CumulativeSum)>100000, 100000, Mod(CumulativeSum,100000)) as NewCumulativeSum
                Resident table1
                Order By Name, Date desc;
                
                

                 

                hope this helps

                 

                regards

                 

                Marco