10 Replies Latest reply: Dec 10, 2014 10:22 AM by Ivan Zdravac RSS

    load script calculation

      Okay guys,

      Example:  (Now I need the possibility to calculate the purple header during the load script)

      Red part I have already in my source

       

      TABLE XYZ:

       

       

       

       

      Field Header:         Order     |         Product      |         Customer       |     Qty. Products on Order     

      Field Content:          A                     small                   John                                      3

                                      A                      big                      John                                     3

                                      A                      small                    John                                    3

                                      B                      small                    Paul                                      1

       

      This works:

       

      TEST:
      LOAD
      A,
      count(A) as QTY_A
      RESIDENT TABLE_XYZ
      group by A;

       

       

      A     3

      B     1

       

      This doesn't work:

      TEST:
      LOAD
      A,
      count(A) as QTY_A,
      if(wildmatch(PRODUCT,'small'),count(A),0) as QTY_small,
      if(wildmatch(PRODUCT,'big'),count(A),0) as QTY_big
      RESIDENT TABLE_XYZ
      group by A;

       

       

      expected result  

       

      A   3    2   1

      B   1    1    0

        • Re: load script calculation

          Okay guys,  (corrected below script) but still doesn't work

          Example:  (Now I need the possibility to calculate the purple header during the load script)

          Red part I have already in my source

           

          TABLE XYZ:

           

           

           

           

          Field Header:         Order     |         Product      |         Customer       |     Qty. Products on Order     

          Field Content:          A                     small                   John                                      3

                                          A                      big                      John                                     3

                                          A                      small                    John                                    3

                                          B                      small                    Paul                                      1

           

          This works:

           

          TEST:
          LOAD
          Order,
          count(Order) as QTY_Order
          RESIDENT TABLE_XYZ
          group by Order;

           

           

          A     3

          B     1

           

          This doesn't work:

          TEST:
          LOAD
          Order,
          count(Order) as QTY_Order,
          if(wildmatch(PRODUCT,'small'),count(Order),0) as QTY_small,
          if(wildmatch(PRODUCT,'big'),count(Order),0) as QTY_big
          RESIDENT TABLE_XYZ
          group by Order;

           

           

          expected result  

           

          A   3    2   1

          B   1    1    0

            • Re: load script calculation
              Liron Baram

              try this one

              TEST:
              LOAD
              Order,
              count(Order) as QTY_Order,
              count(if(PRODUCT='small',Order)) as QTY_small,
              count(if(PRODUCT='big',Order)) as QTY_big
              RESIDENT TABLE_XYZ
              group by Order;

                • Re: load script calculation

                  my first post was wrong did already correction below that it also doesn't work

                   

                  This doesn't work:

                  TEST:
                  LOAD
                  Order,
                  count(Order) as QTY_Order,
                  if(wildmatch(PRODUCT,'small'),count(Order),0) as QTY_small,
                  if(wildmatch(PRODUCT,'big'),count(Order),0) as QTY_big
                  RESIDENT TABLE_XYZ
                  group by Order;   it doesn't work

                   


                    • Re: load script calculation
                      François Cavé

                      Hi Ivan,

                       

                      Could you test this :

                       

                      Data:

                      LOAD * Inline [

                      Order,Product,Customer,Qty. Products on Order    

                      A,small,John,3

                      A,big ,John,3

                      A,small,John,3

                      B,small,Paul,1];

                       

                      Test:

                      LOAD

                        Order,

                        Count(DISTINCT Product) as Qty_Product,

                        Count(DISTINCT Customer) as Qty_Customer,

                        Count(Order) as Qty_ProductsOnOrder

                      Resident Data

                      Group by Order;

                       

                      DROP Table Data;

                       

                      François

                • Re: load script calculation
                  Jeremiah Kurpat

                  Try:

                   

                  TEST:

                  LOAD

                  Order,

                  count(Product) as QTY_small

                  RESIDENT TABLE_XYZ

                  where Product = 'small'

                  Group by Order;

                   

                   

                  Outer Join(TEST)

                  LOAD

                  Order,

                  count(Product) as QTY_big

                  RESIDENT TABLE_XYZ

                  where Product = 'big'

                  Group by Order;

                   

                  Hope this helps!