2 Replies Latest reply: Aug 2, 2010 5:45 PM by Jean-Jacques Jesua RSS

    Specific crosstable

    Jean-Jacques Jesua

      Hi

      I have a table with this structure :

      Name of 3 products sell to client, with amout & quantity

       

      IdProd1Sales1Q1Prod2Sales2Q2Prod3Sales3Q3
      1A10010B7812A564
      2B121C347H562
      3X325U563C246

       

       

      How can I get this new structure more useful in QV :

       

      IdProductSalesQuantity
      1A10010
      1B7812
      1A564
      2B121
      2C347
      2H562
      3X325
      3U563
      3C246

       

      Thanks for your help

      JJJ

       

        • Specific crosstable
          John Witherspoon

          Here's one way:

          Raw:
          LOAD * INLINE [
          Id Prod1 Sales1 Q1 Prod2 Sales2 Q2 Prod3 Sales3 Q3
          1 A 100 10 B 78 12 A 56 4
          2 B 12 1 C 34 7 H 56 2
          3 X 32 5 U 56 3 C 24 6
          ] (delimiter is ' ');

          Products:
          CROSSTABLE (Row, Product)
          LOAD Id, Prod1 as "1", Prod2 as "2", Prod3 as "3"
          RESIDENT Raw;
          Sales:
          CROSSTABLE (Row, Sales)
          LOAD Id, Sales1 as "1", Sales2 as "2", Sales3 as "3"
          RESIDENT Raw;
          Quantities:
          CROSSTABLE (Row, Quantity)
          LOAD Id, Q1 as "1", Q2 as "2", Q3 as "3"
          RESIDENT Raw;

          Final:
          NOCONCATENATE LOAD * RESIDENT Products;
          LEFT JOIN (Final) LOAD * RESIDENT Sales;
          LEFT JOIN (Final) LOAD * RESIDENT Quantities;

          DROP TABLES Products, Sales, Quantities;
          DROP FIELD Row;