10 Replies Latest reply: Mar 6, 2012 9:36 AM by Celambarasan Adhimulam RSS

    Next & previous syntax for NumberOrder

      Hello,

       

      I'm new at Qlickview and hope anyone can help me with the following:

      I have customerdata with customernumbers and ordernumbers and want to know the NumberOrder; meaning per customer if it's the first, second or third (or more) order.

       

      I thought of using next and previous syntax (although i'm not sure what those look like), and tried the following (new formulas in bold):

       

      Load

      //    "No_" as DocumentNo

         "Document No_"&'-'&"No_" as DocuItemNo

         , "Sell-to Customer No_"as CustomerNo

         , "Package Tracking No_" as PackageTrackingNo

         , "Promotion Name" as PromotionName

         , "Promotion ID" as PromotionID

         , "Discount Description" as DiscountDescription

         , IF (NEXT(CustomerNo)=CustomerNo, CustomerNo,1) AS OrderCode 

      // so for customers who placed more orders, those orders will be coded with 1 and then we can calculate NumberOrder

         , IF (PREVIOUS(CustomerNo)=CustomerNo, PREVIOUS(OrderCode)+1,1) as NumberOrder;   

       

      SQL SELECT  

             "Document No_"

         , "Sell-to Customer No_"   

         , "No_"  

         , "Package Tracking No_"

         , "Promotion Name"  

         , "Promotion ID"

         , "Discount Description" 

      FROM "$(DB_DATASOURCE)".dbo."$(DB_COMPANY)$Sales Invoice Line"

      ORDER BY "Sell-to Customer No_";

       

       

      But this is not working... Does anyone have a solution?

       

      Regards,

      Eline

        • Re: Next & previous syntax for NumberOrder
          Celambarasan Adhimulam

          Hi,

               Rename the "Sell-to Customer No_" field in the database side itself.

               Check with this.

           

          Load

          //    "No_" as DocumentNo

             "Document No_"&'-'&"No_" as DocuItemNo

             , CustomerNo

             , "Package Tracking No_" as PackageTrackingNo

             , "Promotion Name" as PromotionName

             , "Promotion ID" as PromotionID

             , "Discount Description" as DiscountDescription

          // so for customers who placed more orders, those orders will be coded with 1 and then we can calculate NumberOrder

             , IF (PREVIOUS(CustomerNo)=CustomerNo, PREVIOUS(NumberOrder)+1,1) as NumberOrder;\\IF (Peek(CustomerNo)=CustomerNo, Peek(NumberOrder)+1,1)

          SQL SELECT 

                 "Document No_"

             , "Sell-to Customer No_"  as CustomerNo 

             , "No_" 

             , "Package Tracking No_"

             , "Promotion Name" 

             , "Promotion ID"

             , "Discount Description"

          FROM "$(DB_DATASOURCE)".dbo."$(DB_COMPANY)$Sales Invoice Line"

          ORDER BY "Sell-to Customer No_";

           

          Celambarasan

            • Next & previous syntax for NumberOrder

              Thanks guys, but unfortunately it's not working yet... I get the following comment when running the script:

              Error in expression: previous takes 1 parameter

               

              Any idea what that means and how i can solve it?

                • Next & previous syntax for NumberOrder
                  Celambarasan Adhimulam

                  Hi,

                       Can you post your script as it is which your using now?

                   

                  Celambarasan

                  • Next & previous syntax for NumberOrder
                    jagan mohan rao appala

                    Hi,

                     

                    Previous() has only parameter, did you give more than one?  Attach the script you used.

                     

                    Regards,

                    Jagan.

                      • Next & previous syntax for NumberOrder

                        Hi,

                         

                        I don't know how to attatch a complete script, but right now it looks like this (following your suggestions):

                         

                        CustomerDocument:

                        Load

                        //    "No_" as DocumentNo

                           "Document No_"&'-'&"No_" as DocuItemNo

                           , CustomerNo

                           , "Package Tracking No_" as PackageTrackingNo

                           , "Promotion Name" as PromotionName

                           , "Promotion ID" as PromotionID

                           , "Discount Description" as DiscountDescription

                           , IF (PREVIOUS(CustomerNo)=CustomerNo, PREVIOUS(NumberOrder+1,1) as NumberOrder;  

                         

                        SQL SELECT  

                               "Document No_"

                           , "Sell-to Customer No_" as CustomerNo   

                           , "No_"  

                           , "Package Tracking No_"

                           , "Promotion Name"  

                           , "Promotion ID"

                           , "Discount Description" 

                        FROM "$(DB_DATASOURCE)".dbo."$(DB_COMPANY)$Sales Invoice Line"

                        ORDER BY "Sell-to Customer No_";

                         

                        and then i get the error: previous takes only one parameter. So I don't know what i've done wrong.

                         

                        Thanks again for your help,

                        Eline

                          • Next & previous syntax for NumberOrder
                            Celambarasan Adhimulam

                            Hi,

                                 You missed paranthesis PREVIOUS(NumberOrder)+1

                            Check with the below script.

                            CustomerDocument:

                            Load

                            //    "No_" as DocumentNo

                               "Document No_"&'-'&"No_" as DocuItemNo

                               , CustomerNo

                               , "Package Tracking No_" as PackageTrackingNo

                               , "Promotion Name" as PromotionName

                               , "Promotion ID" as PromotionID

                               , "Discount Description" as DiscountDescription

                               , IF (PREVIOUS(CustomerNo)=CustomerNo, PREVIOUS(NumberOrder)+1,1) as NumberOrder

                             

                            SQL SELECT 

                                   "Document No_"

                               , "Sell-to Customer No_" as CustomerNo  

                               , "No_" 

                               , "Package Tracking No_"

                               , "Promotion Name" 

                               , "Promotion ID"

                               , "Discount Description"

                            FROM "$(DB_DATASOURCE)".dbo."$(DB_COMPANY)$Sales Invoice Line"

                            ORDER BY "Sell-to Customer No_";

                             

                            Celambarasan

                              • Next & previous syntax for NumberOrder

                                Thanks Celambarasan, i did not get the error this time.

                                 

                                But... i'm getting another one: cannot find field <NumberOrder>

                                 

                                This doesn't surprise me since NumberOrder is a field that I haven't defined before. And i tried to solve this by defining this before i use the previous function (see my first post) but then i found out that NEXT is not a function in Qlickview, so that obviously didn't work. Do you have another solution?

                                 

                                Regards,

                                Eline

                                  • Re: Next & previous syntax for NumberOrder
                                    Celambarasan Adhimulam

                                    Hi,

                                         Then try with peek function instead of Previous.

                                     

                                    CustomerDocument:

                                    Load

                                    //    "No_" as DocumentNo

                                       "Document No_"&'-'&"No_" as DocuItemNo

                                       , CustomerNo

                                       , "Package Tracking No_" as PackageTrackingNo

                                       , "Promotion Name" as PromotionName

                                       , "Promotion ID" as PromotionID

                                       , "Discount Description" as DiscountDescription

                                       , IF (PEEK('CustomerNo')=CustomerNo, PEEK('NumberOrder')+1,1) as NumberOrder;

                                     

                                    SQL SELECT

                                           "Document No_"

                                       , "Sell-to Customer No_" as CustomerNo 

                                       , "No_"

                                       , "Package Tracking No_"

                                       , "Promotion Name"

                                       , "Promotion ID"

                                       , "Discount Description"

                                    FROM "$(DB_DATASOURCE)".dbo."$(DB_COMPANY)$Sales Invoice Line"

                                    ORDER BY "Sell-to Customer No_";

                                     

                                    Celambarasan

                                     

                                    Edit:Added single quotes in the peek function. Message was edited by: Celambarasan Adhimulam

                            • Next & previous syntax for NumberOrder
                              Rahul Gupta

                              Use This:

                               

                               

                              TableName:

                              Load

                                 'Document No_'&'-'&'No_' as DocuItemNo

                                 , CustomerNo

                                 , 'Package Tracking No_' as PackageTrackingNo

                                 , 'Promotion Name' as PromotionName

                                 , 'Promotion ID' as PromotionID

                                 , 'Discount Description' as DiscountDescription

                                 , IF (Peek(CustomerNo)=CustomerNo, (Peek(NumberOrder)+1),1) as NumberOrder;

                              SQL SELECT

                                     "Document No_"

                                 , "Sell-to Customer No_"  as CustomerNo

                                 , "No_"

                                 , "Package Tracking No_"

                                 , "Promotion Name"

                                 , "Promotion ID"

                                 , "Discount Description"

                              FROM "$(DB_DATASOURCE)".dbo."$(DB_COMPANY)$Sales Invoice Line"

                              ORDER BY "Sell-to Customer No_";

                               

                              Regards

                               

                              Rahul Gupta

                          • Re: Next & previous syntax for NumberOrder
                            jagan mohan rao appala

                            Hi,

                             

                            There is no Next() in qlikview, we have Previous() using this we can arrive the Number order.

                             

                            TableName:

                            Load

                               'Document No_'&'-'&'No_' as DocuItemNo

                               , CustomerNo

                               , 'Package Tracking No_' as PackageTrackingNo

                               , 'Promotion Name' as PromotionName

                               , 'Promotion ID' as PromotionID

                               , 'Discount Description' as DiscountDescription

                               , IF (PREVIOUS(CustomerNo)=CustomerNo, PREVIOUS(NumberOrder)+1,1) as NumberOrder;

                            SQL SELECT 

                                   "Document No_"

                               , "Sell-to Customer No_"  as CustomerNo 

                               , "No_" 

                               , "Package Tracking No_"

                               , "Promotion Name" 

                               , "Promotion ID"

                               , "Discount Description"

                            FROM "$(DB_DATASOURCE)".dbo."$(DB_COMPANY)$Sales Invoice Line"

                            ORDER BY "Sell-to Customer No_";

                             

                            Regards,

                            Jagan.