21 Replies Latest reply: Oct 14, 2011 9:43 AM by Deepak Vadithala RSS

    Expression

    sandeepa rout

      Hi All,

       

      Please Suggest how to write this in expression

       

      Set TempSum=0;

      Set RowCount= 162522;

       

       

      for(RowCount<= 162522)

        if(NoOfPallets <> 0)

        TempSum= TempSum+ Pallets;

      RowCount++;

       

       

      Thanks.

        • Expression
          Miguel Angel Baeyens de Arce

          Hi Sandeepa,

           

          Is that an expression in a chart? Or a loop in the script? Can you please elaborate on your requirements and expected returns?

           

          Miguel Angel Baeyens

          BI Consultant

          Comex Grupo Ibérica

            • Re: Expression
              sandeepa rout

              Query is this :

               

              Set TempSum=0;

               

              Let y= noofrows('Data')

              for i=1 to $(y)

               

              if(NoOfPallets <> 0)

               

               

              TempSum= TempSum+ NoOfPallets;

               

              y++;

                • Expression
                  Sunil Chauhan

                  hope this

                   

                  Set TempSum=0;

                   

                  Let y= noofrows('Data')

                  for i=1 to $(y)

                   

                  if(NoOfPallets <> 0)

                   

                  load

                   

                  TempSum+ NoOfPallets as TempSum

                  autogenrate 1;

                   

                  y++;

                    • Re: Expression
                      sandeepa rout

                      Please Correct it:

                       

                      Data:

                      LOAD  CustNo,

                                ItemCode,

                                OrderNo,

                                 OrderQty,

                           DeliveredQty,

                          InvoiceDate,

                           Month([Invoice Date]) as Month,

                          'Q' & ceil(month([Invoice Date]) / 3)    AS Quarter,

                          InvoiceNo,

                           InvoiceAmount,

                           Pallet,

                           Carton,

                           Shrink,

                           [No Of Pallets] as NoOfPallets,

                           [No Of Cartons] as NoOfCartons,

                           [No Of Shrinks] as NoOfShrinks,

                           Loose

                      FROM

                      [C:\Users\sandeepa_rout\Desktop\Task-3\Delivery AnalysisFlatFile.txt]

                      (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

                       

                       

                      Let y= noofrows('Data');

                      Join(Data)

                      Load

                      for i=1 to $(y)

                      if(NoOfPallets <> 0, TempSum+ NoOfPallets) as TempSum,

                      autogenerate 1;

                      y++;

                        • Expression
                          Sunil Chauhan

                          what to correct its look like ok

                           

                           

                          take TempSum into expression and see the result

                           

                           

                          if not please be crear what you want.

                            • Re: Expression
                              sandeepa rout

                              Let y= noofrows('Data');

                              Join(Data)

                              Load

                              for i=1 to $(y)

                              if(NoOfPallets <> 0, TempSum+ NoOfPallets) as TempSum,

                              autogenerate 1;

                              y++;

                               

                               

                              It's giving syntax error.

                                • Expression
                                  Sunil Chauhan

                                  use this

                                   

                                  Let y= noofrows('Data');

                                  Join(Data)

                                  Load

                                  for i=1 to $(y)

                                  if(NoOfPallets <> 0, TempSum+ NoOfPallets) as TempSum// here comma is not required

                                  autogenerate 1;

                                  y++;

                                    • Re: Expression
                                      sandeepa rout

                                      Still it is giving error.

                                      " y++ Unknown"

                                      Please find attached of the error file.

                                        • Expression
                                          Sunil Chauhan

                                          i believe no need to join because there is no key fieeld

                                          use the below code

                                           

                                          Let y= noofrows('Data');

                                          Load

                                          for i=1 to $(y)

                                          if(NoOfPallets <> 0, TempSum+ NoOfPallets) as TempSum// here comma is not required

                                          autogenerate 1;

                                          y++;

                                           

                                          hope this help

                                            • Expression
                                              Sunil Chauhan

                                              use the coe below

                                               

                                              and reload

                                               

                                               

                                              Data:

                                              Data:
                                              LOAD [Customer Number] as CustNo,
                                                   [Customer Name] as CustName,
                                                   [Item Code] as ItemCode,
                                                   [Item Description] as ItemDesc,
                                                   [Order Number] as OrderNo,
                                                   [Order Position] as OrderPosition,
                                                   BackOrder ,
                                                   [Order Qty] as OrderQty,
                                                   [Delivered Qty] as DeliveredQty,
                                                   [Invoice Date] as InvoiceDate,
                                                   Month([Invoice Date]) as Month,
                                                  'Q' & ceil(month([Invoice Date]) / 3)    AS Quarter,
                                                   [Invoice Number] as InvoiceNo,
                                                   [Invoice Amount] as InvoiceAmount,
                                                   [Line of Business] as LOB,
                                                   [Line Of Business Description] as LOBDesc,
                                                   Warehouse,
                                                   Pallet,
                                                   Carton,
                                                   Shrink,
                                                   [No Of Pallets] as NoOfPallets,
                                                   [No Of Cartons] as NoOfCartons,
                                                   [No Of Shrinks] as NoOfShrinks,
                                                   Loose
                                              FROM
                                              [C:\Users\sandeepa_rout\Desktop\Task-3\Delivery AnalysisFlatFile.txt]
                                              (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

                                              Let y= noofrows('Data');
                                              Set TempSum=0;

                                               

                                              for i=1 to $(y)
                                              Load
                                              if(NoOfPallets <> 0, TempSum+ NoOfPallets) as TempSum

                                              autogenerate 1;

                                              next i;

                                               

                                               

                                              try this

                                                • Re: Expression
                                                  sandeepa rout

                                                  Now it is giving different error " Field not found error" .

                                                   

                                                  " NoOfPallets Field Not found"

                                                    • Expression
                                                      Sunil Chauhan

                                                      what about this

                                                       

                                                       

                                                      Data:
                                                      LOAD [Customer Number] as CustNo,
                                                           [Customer Name] as CustName,
                                                           [Item Code] as ItemCode,
                                                           [Item Description] as ItemDesc,
                                                           [Order Number] as OrderNo,
                                                           [Order Position] as OrderPosition,
                                                           BackOrder ,
                                                           [Order Qty] as OrderQty,
                                                           [Delivered Qty] as DeliveredQty,
                                                           [Invoice Date] as InvoiceDate,
                                                           Month([Invoice Date]) as Month,
                                                          'Q' & ceil(month([Invoice Date]) / 3)    AS Quarter,
                                                           [Invoice Number] as InvoiceNo,
                                                           [Invoice Amount] as InvoiceAmount,
                                                           [Line of Business] as LOB,
                                                           [Line Of Business Description] as LOBDesc,
                                                           Warehouse,
                                                           Pallet,
                                                           Carton,
                                                           Shrink,
                                                           [No Of Pallets] as NoOfPallets,
                                                           [No Of Cartons] as NoOfCartons,
                                                           [No Of Shrinks] as NoOfShrinks,
                                                           Loose
                                                      FROM
                                                      [C:\Users\sandeepa_rout\Desktop\Task-3\Delivery AnalysisFlatFile.txt]
                                                      (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

                                                      Let y= noofrows('Data');
                                                      Set TempSum=0;

                                                       

                                                      for i=1 to $(y)
                                                      Load

                                                       

                                                      if(NoOfPallets <> 0, TempSum+ NoOfPallets) as TempSum

                                                      Resident Data;

                                                       

                                                      next i;

                                                        • Re: Expression
                                                          sandeepa rout

                                                          Now it is saying "TempSum field is not found".

                                                            • Re: Expression
                                                              Miguel Angel Baeyens de Arce

                                                              Hi,

                                                               

                                                              Did you try mi suggestion below? TempSum is not a field in table "Data", hence the error. Probably using $(TempSum) instead because I'm assuming you are referring to the previously set variable TempSum, isn't it?

                                                               

                                                              Regards.

                                                                • Re: Expression
                                                                  sandeepa rout

                                                                  Yes, Your right.

                                                                   

                                                                  I am not very familiar with qlikview . Hence facing difficulties to write the expression using peak(), rangesum().

                                                                  Can you please tell me how to write the specified query using peak() and rangesum().

                                                                   

                                                                  Many Thanks.

                                                                    • Re: Expression
                                                                      Miguel Angel Baeyens de Arce

                                                                      Hi Sandeepa,

                                                                       

                                                                      Using the following script, a new field called TempSum will be created and it will be populated with the sum of TempSum, taking into account that if NoOfPallets is equal to zero, the value will be 1, otherwise, it will accumulate from previous values.

                                                                       

                                                                      Data:
                                                                      LOAD CustNo,
                                                                           ItemCode,
                                                                           OrderNo,
                                                                           OrderQty,
                                                                           DeliveredQty,
                                                                           InvoiceDate,
                                                                           Month([Invoice Date]) as Month,
                                                                           'Q' & ceil(month([Invoice Date]) / 3)    AS Quarter,
                                                                           InvoiceNo,
                                                                           InvoiceAmount,
                                                                           Pallet,
                                                                           Carton,
                                                                           Shrink,
                                                                           [No Of Pallets] as NoOfPallets,
                                                                           If([No Of Pallets] <> 0, RangeSum(Peek('TempSum'), 1), 1) AS TempSum,
                                                                           [No Of Cartons] as NoOfCartons,
                                                                           [No Of Shrinks] as NoOfShrinks,
                                                                           Loose
                                                                      FROM
                                                                      [C:\Users\sandeepa_rout\Desktop\Task-3\Delivery AnalysisFlatFile.txt]
                                                                      (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);
                                                                      

                                                                       

                                                                      However, this field does not make much sense to me as it is, my guess is that you will need the accumulation of NoOfPallets monthly, quarterly or something. Please read and check the working examples and applications in the posts I mentioned below.

                                                                       

                                                                      Hope that helps.

                                                                       

                                                                      Miguel Angel Baeyens

                                                                      BI Consultant

                                                                      Comex Grupo Ibérica

                                                                    • Expression
                                                                      Sunil Chauhan

                                                                      tempsum is variable which is always use with $ sign

                                                                       

                                                                      so use $(TempSum)

                                                                       

                                                                      or

                                                                      may be you required to writein single quotes  '$(TempSum)'

                                                  • Re: Expression
                                                    Miguel Angel Baeyens de Arce

                                                    Hi,

                                                     

                                                    Why not using Peek()? If you want to get an aggregated value in the script you don't need to loop, you can get it using RangeSum(), Peek(), Previous() and so, as shown in this thread and this post among some others. (Some with working examples).


                                                    Hope that helps.

                                                     

                                                    Miguel Angel Baeyens

                                                    BI Consultant

                                                    Comex Grupo Ibérica