13 Replies Latest reply: Sep 1, 2011 3:45 AM by Byron Van Wyk RSS

    Previous and Peek - Script

    Byron Van Wyk

      Hi Guys,

       

      Both my previous and peek functions on the same field return a value on the same line. I assumed that previous would check my if statement for the previous line and peek the next line. What am I doing wrong or not understanding. Also, if this doesnt make sense let me know (of course) and I will add a table as an example

       

       

      Cheers,

      Byron

        • Previous and Peek - Script

          Hi Byron,

           

          Previous looks in the previous line of the data being loaded (not discarded by a where clause), not from the table that is being created in QlikView. You have to use the column names of the source table, not the column names of the table in QV.

          Peek looks into the table that is being created in the QV data cloud. This is done at runtime while the table is being populated by the script. It is thus not possible to look ahead to the next row in a load statement.

           

          Regards,

           

          Bert

          • Previous and Peek - Script
            Liron Baram

            hei

            both functions work on the pervious records

             

            previous refers to the previous record only

             

            while in pick you can spcifey the numer of row you want to go to

             

            post an example please so i can understand your problem

            • Previous and Peek - Script
              Anand Chouhan

              Hi,

               

              Could you share any sample file for that it goes easy.

               

              Regards,

              Anand

                • Re: Previous and Peek - Script
                  Sunil Chauhan

                  please see the attched file

                   

                   

                   

                  previous used to pull previous record

                   

                  where as peek is used to pull both first and last record

                   

                  using -1 and 0.

                    • Re: Previous and Peek - Script
                      Pieter Boots

                      By using 'asc' or 'desc' in the order by clause you can get the previous or the next value.

                       

                      example

                       

                      tmp:

                      load * inline

                      [id,name

                      1,'Jack'

                      2,'John'

                      3,'Mike'

                      4,'Nic'

                      ];

                       

                       

                      example1:

                      load

                      id,

                        name as name1,

                      previous(name) as prev

                      resident tmp

                      order by id asc;

                       

                       

                       

                      example2:

                      load

                      id,

                        name as name2,

                      previous(name) as next

                      resident tmp

                      order by id desc;

                       

                      qlikview.png

                        • Previous and Peek - Script
                          Byron Van Wyk

                          Thanks to all. Every response has been informative, all deserves helpful answers. I think it best to show you guys what I am trying to do and maybe someone can point me in the right direction. I may whiile be doing this all wrong, we will find out

                           

                           

                          DuplicatePrefinal:
                          LOAD *
                          Where d_duplicate = 'Check';
                          LOAD
                          ClientName,
                          TempName as d_tempname,
                          CategoryDesc as d_categorydesc,
                          WkEndDate as d_wkenddate,
                          TimesheetNo as d_timesheetno,
                          Invoice as d_invoice,
                          Duplicate as d_duplicate,
                          Hours as d_hours,
                          if(Hours<0,'N','P') as d_signhours,
                          Fabs(Hours) as d_positivehours,
                          Fabs(ChargeValue) as d_positivechargevalue,
                          ChargeValue as d_chargevalue
                          Resident SPInvoicingPreFinal
                          Order by TempName, WkEndDate, CategoryDesc, TimesheetNo, Invoice;

                          DuplicateFinal:
                          LOAD *,
                          rowno() as d_rownoID,
                          1
                          as Dummy, //Dummy field to not force automatic concatenation
                          /* EXAMPLE OF CODE I WANT TO ADD TO DO SOMETHING */
                          if(peek(d_positivehours)<>d_positivehours /*current line*/ and d_signhours<>'N',1) as Remove
                          Resident DuplicatePrefinal
                          Order by d_tempname, d_wkenddate, d_categorydesc, d_positivehours, d_timesheetno, d_invoice;
                          Drop Table DuplicatePrefinal;
                          Drop Field Dummy;


                          The above script is pretty much the part that I am having problem with. Essentially, refer to the comment lines. The peek function I use I want to add a null value to the line items. I will then suppress this as a dimension in my straight table as I want to exclude from the table I have already manipulated. I guess posting the entire script is not the best way to go, but hoping one of you brilliant people understands what I am trying to do. PLEASE, if there is a better approach, PLEASE DO TELL

                          Many thanks in advance!

                            • Re: Previous and Peek - Script
                              Miguel Angel Baeyens de Arce

                              Byron,

                               

                              Check the following modified script

                               

                              DuplicatePrefinal:
                              LOAD ClientName,
                                   TempName AS d_tempname,
                                   CategoryDesc AS d_categorydesc,
                                   WkEndDate AS d_wkenddate,
                                   TimesheetNo AS d_timesheetno,
                                   Invoice AS d_invoice,
                                   Duplicate AS d_duplicate,
                                   Hours AS d_hours,
                                   If(Hours < 0, 'N', 'P') AS d_signhours,
                                   Fabs(Hours) AS d_positivehours,
                                   Fabs(ChargeValue) AS d_positivechargevalue,
                                   ChargeValue AS d_chargevalue 
                              Resident SPInvoicingPreFinal
                              WHERE Duplicate = 'Check' // set the WHERE here, save one preceding load
                              Order by TempName, WkEndDate, CategoryDesc, TimesheetNo, Invoice;
                              
                              DuplicateFinal:
                              NOCONCATENATE LOAD *,
                                   RowNo() AS d_rownoID,
                                   // 1 as Dummy, // No longer need this
                                   // the following compares the previous loaded value of d_positivehours field against the current
                                   If(Previous(d_positivehours) <> d_positivehours AND d_signhours <> 'N', 1) AS Remove
                              Resident DuplicatePrefinal
                              Order by d_tempname, d_wkenddate, d_categorydesc, d_positivehours, d_timesheetno,d_invoice;
                              
                              
                              Drop Table DuplicatePrefinal;
                              
                              
                              Drop Field Dummy;
                              

                               

                              Hope that helps.

                               

                              Miguel Angel Baeyens

                              BI Consultant

                              Comex Grupo Ibérica

                              • Previous and Peek - Script
                                Kaushik Solanki

                                Hi,

                                 

                                    Try this way.

                                 

                                   

                                DuplicatePrefinal:
                                LOAD *
                                Where d_duplicate = 'Check';


                                LOAD
                                ClientName,
                                TempName as d_tempname,
                                CategoryDesc as d_categorydesc,
                                WkEndDate as d_wkenddate,
                                TimesheetNo as d_timesheetno,
                                Invoice as d_invoice,
                                Duplicate as d_duplicate,
                                Hours as d_hours,
                                if(Hours<0,'N','P') as d_signhours,
                                Fabs(Hours) as d_positivehours,
                                Fabs(ChargeValue) as d_positivechargevalue,
                                ChargeValue as d_chargevalue
                                Resident SPInvoicingPreFinal
                                Order by TempName, WkEndDate, CategoryDesc, TimesheetNo, Invoice;

                                 

                                 

                                DuplicateFinal:
                                LOAD *,
                                rowno() as d_rownoID,
                                1 as Dummy,

                                if(peek('d_positivehours',rowno(),'DuplicatePrefinal')<>d_positivehours /*current line*/ and d_signhours<>'N',1) as Remove
                                Resident DuplicatePrefinal
                                Order by d_tempname, d_wkenddate, d_categorydesc, d_positivehours, d_timesheetno, d_invoice;
                                Drop Table DuplicatePrefinal;
                                Drop Field Dummy;

                                 

                                 

                                Regards,

                                Kaushik Solanki

                                  • Re: Previous and Peek - Script
                                    Byron Van Wyk

                                    Thanks Miguel and Kaushik. I will try both solutions now. In the meantime I have created a test QVD document that hopefully explains the problem a little better. As mentioned will try the above solutions quickly and let you know

                                      • Re: Previous and Peek - Script
                                        Byron Van Wyk

                                        BTW I must state. You may look at the above document and say while the sum of those values is 0 so it is removed. Yes true, but I have used group by's to pull a specific table in a specific order. However, the ordering always ensures that these two values are next to each so if there is a way to sum them to be removed, then great

                                          • Re: Previous and Peek - Script
                                            Byron Van Wyk

                                            Hi Again all, apologies for the multiple posts. The below code, taken from miguel now adds a 1 to one line. As shown in the attached QVD, i now need to add a 1 to the previous line. I can always later edit this so it is a null, for now, using a 1 is a lot easier. I thought the below would work, but it doesnt. The idealogy behind it is what I need to get to work

                                            DuplicateFinal:
                                            NOCONCATENATE
                                            LOAD *,
                                            rowno() as d_rownoID,
                                            If(Previous(d_positivehours) = d_positivehours AND d_signhours = 'N', 1) AS Remove
                                            Resident DuplicatePrefinal
                                            Order by d_tempname, d_wkenddate, d_categorydesc, d_positivehours, d_timesheetno, d_invoice;
                                            Drop Table DuplicatePrefinal;

                                            FinalDuplicate:
                                            LOAD
                                            if(peek(Remove)=1,1) as Remove
                                            Resident DuplicateFinal;

                                             

                                              • Re: Previous and Peek - Script
                                                Byron Van Wyk

                                                Hi All,

                                                 

                                                I managed to put together some code that gave me the correct result. In my straight table, I now have a if clause to supress any null values where Ignore = X. Here is the code if anyone is interested


                                                DuplicatePrefinal:
                                                LOAD
                                                ClientAccount,
                                                TempName as d_tempname,
                                                CategoryDesc as d_categorydesc,
                                                WkEndDate as d_wkenddate,
                                                TimesheetNo as d_timesheetno,
                                                Invoice as d_invoice,
                                                Duplicate as d_duplicate,
                                                Hours as d_hours,
                                                if(Hours<0,'N','P') as d_signhours,
                                                Fabs(Hours) as d_positivehours,
                                                Fabs(ChargeValue) as d_positivechargevalue,
                                                ChargeValue as d_chargevalue,
                                                PayValue as d_payvalue
                                                Resident InvoicingPreFinal
                                                Where Duplicate = 'Check'
                                                Order by TempName, WkEndDate, CategoryDesc, TimesheetNo, Invoice;


                                                DuplicateFinal:
                                                NOCONCATENATE
                                                LOAD *,
                                                rowno() as d_rownoID
                                                Resident DuplicatePrefinal
                                                Order by d_tempname, d_wkenddate, d_categorydesc, d_positivehours, d_signhours desc, d_timesheetno, d_invoice, d_positivechargevalue desc;
                                                Drop Table DuplicatePrefinal;
                                                ReversalsRemovePrefinal:


                                                Qualify *;
                                                UNQUALIFY d_rownoID;
                                                LOAD *
                                                Resident DuplicateFinal
                                                Where d_signhours='N' and previous(d_hours)=d_positivehours and previous(d_chargevalue)=d_positivechargevalue and not isnull(d_tempname);
                                                UNQUALIFY *;

                                                Concatenate(ReversalsRemovePrefinal)
                                                LOAD (d_rownoID - 1) as d_rownoID,
                                                ReversalsRemovePrefinal.d_tempname
                                                Resident ReversalsRemovePrefinal;



                                                ReversalsRemoveFinal:
                                                LOAD
                                                d_rownoID as ReversalsRemoveFinal.d_rownoID,
                                                d_rownoID,
                                                'X'
                                                as d_ignore
                                                Resident ReversalsRemovePrefinal
                                                Where not isnull(ReversalsRemovePrefinal.d_tempname);
                                                Drop Table ReversalsRemovePrefinal;

                                                 

                                • Previous and Peek - Script
                                  Miguel Angel Baeyens de Arce

                                  Hi Byron,

                                   

                                  Check this post or this other post that faces an similar issue. Both are examples on the use of Previous() and Peek() and you will find many more digging a bit in the Community.

                                   

                                  As Liron says above, both Previous and Peek refer to already loaded values. The main difference is that Previous() refers only to the immediately previously loaded record, while Peek() may return any existing value from a field.

                                   

                                  Hope that helps.

                                   

                                  Miguel Angel Baeyens

                                  BI Consultant

                                  Comex Grupo Ibérica