9 Replies Latest reply: Sep 16, 2016 7:15 AM by Michael Behrend RSS

    Scripting Question

    Michael Behrend

      Hello Comunity i have a Question and a problem,

       

      at the moment i have a Pivot table with this informations

       

      Groupdocument numberArtikelcarton quantityunit pricepiece quantitytotal priceif(carton quantity = piece quantity,'0','1')
      cup1Chicken 4x5405400
      cup 2Beef 9x504501
      cup2Shrimp 9x504501
      bag3Fish 5x2002000
      bag4Curry 7x5502000

       

       

      this table is to look at artikel which went out for free.

      Now i saw all artikel which have the same carton quantity and piece quantity are on a set.

      The Set Artikel must be removed from the table because they dont go out for free the price is on the head from the set.


      The artikel which have different numbers on carton quantity and piece quantity go out for free.

      The different between Piece quantity and carton quantity is: Beef has 9 Cups in a Carton so we have 5 cartons with 9 cups = 45

       

      importend is if(carton quantity = piece quantity,'0','1') this shows me which artikel has the same carton quantity and piece quantity.


      Now my question: How can i FIlter it that i have only Artikel with different carton quantity and piece quantity, have a one on the end.

      I need it to count the the Artikel which go out for free.


      The informations are all from one Table.

        • Re: Scripting Question
          Jakub Szurogajło

          Hello,

           

          What are the expressions in your pivot table?

          Is "Suppress Zero Values" ticked in Presentation tab of the pivot table?

           

          Can't you maybe create a flag during data load and simply filter proper expression based on this flag? (using set analysis)

           

          BR,

          Kuba

            • Re: Scripting Question
              Michael Behrend

              The expressions from the pivot is.

              To see how many artikel go out for free.

              The Document number explain it self, carton quantity is normaly the quntity how many cartons are in the order. Unit price is here 0 because i use a filter to show me all products with a unite price 0 normly it show's me the price from one carton. Pieces are the pieces in a carton. Total Price is the Price from all cartons in the order together to explain you have 5 cartons one carton costs 1€ also 5*1=5.

               

              I found out when carton quantity and the piece quantity are the same, this artikel/order went out for free, when it's different the artikel/order didn't go out for free this artikel are on a set and this is the problem set artikel are shown with a unit/total price 0.

               

              I saw it with this formel if(carton quantity = piece quantity,'0','1'


              and now i will filter this but the problem is i get the informations from a other qlikview data via binary.


              and i can't do a filter wth flag there.

            • Re: Scripting Question
              Philipp Austermeier

              Hello Michael,

               

              i would move this "if(carton quantity = piece quantity,'0','1')" to the script as a flag like:


              load
              *,
              if(carton quantity = piece quantity,'0','1') as _FlagBlaBla
              from
              
              
              

               

              And then sum(<_FlagBlaBla = {1}>} total_price) for example.


              edit: always bear in mind: "Move all complexitiy you can to the script"

              --> So for this example use Flags!

                • Re: Scripting Question
                  Michael Behrend

                  Hey thx the idea is great, but i have a problem i get my informations from a other data vie Binary.

                   

                  i can't creat this flag because on the main data it's not working.

                   

                  The carton quanitity is a fix information from the table but the piece quantity is from a other if statement

                   

                  if(Upper(Einheit)='BUNDLE'or Upper(Einheit)='KARTON'or Upper(Einheit)='STÜCK',Mengetmp*Mengeneinheit,Mengetmp)as piece quantity

                   

                  When i use  if(carton quantity = piece quantity,'0','1') as Flag


                  i get an error he can't find the Table.


                • Re: Scripting Question
                  sasi k

                  HI,

                  If not able to do it from script then,

                  try this exp in place of Piecequantity

                  =sum(Aggr(if(cartonquantity=sum(piecequantity),sum(piecequantity)),Group,Artikel,cartonquantity,documentnumber))


                  Please find the attached application for reference

                    • Re: Scripting Question
                      Michael Behrend

                      Hi thanks for you help,

                       

                      but i have no idea how can this help me.

                       

                      The think is Piecequantity is from a other if statement and these numbers are correct my main to look at the quantity. I compare the numbers in Piecequantity with an excell sheet where the right numbers are inside.


                      So i cant change

                      if(Upper(Einheit)='BUNDLE'or Upper(Einheit)='KARTON'or Upper(Einheit)='STÜCK',Mengetmp*Mengeneinheit,Mengetmp)as piece quantity


                      I need something what do this :

                      Sum piece quantity when carton quantity and piece quantity are not the same.

                      And i have no idea how i can do this.


                        • Re: Scripting Question
                          sasi k

                          HI,

                          your piece quantity is a caluculated fied from script and

                          you want to keep records where Piece quantity = Cartoon Quantity * Volume , so you can use my exp else share your sample app/data .

                            • Re: Scripting Question
                              Michael Behrend

                              The problem is i cant show here the real script from our company. I i changed you exp on for ours but it shows me nothing the field is empty. I have no idea whta is wrong. I have on the pivot dimension produktgroup is an if to filter somethinks documentnumber is fix datum fix artikelnumber fix artikel fix then the fomels quantity karton is sum from one table Actual piece is this sum( {$} Menge) Brutto win is sum( {$} UmsatzBrutto) and this is all what i have. And now i have no idea how i can this filter.