4 Replies Latest reply: Dec 15, 2009 9:23 AM by zysus RSS

    Dynamically load numerous tables by a dimension

      Hi there,

      is it possible to load Data from a datasource dynamicaly into own QV-tables separated by a dimension?

      An example, the data is provided like this:
      Customer, Productgroup, Status

      0001, Screws, Yes

      0001, Nails, No

      0001, Glue, Yes

      0002, Screws, Yes

      0002, Nails, No

      0002, Glue, Yes

      0003, Screws, No

      0003, Nails, No

      0003, Glue, No

      (Status says if a customer ever bought the product)

      I would like to load an own table for each group of products automatically. In this case that would be Screws, Glue and Nails. The tables shall contain the customer number and a status, that indicates if the customer uses the product or not.

      We want to see: "Who buys the screws, but never bought Nails?" For that I would set the Screw-Status to yes and the Nail-Status to No. By just loading the given table, this is not possible, I think I need own tables for each value of my dimension.

      Sorry for my bad english, I hope the questions comes across. Does somebody know how to do this?

      Thanks in advance and best greetings

      Martin

        • Dynamically load numerous tables by a dimension

          Hi,

          With the kind of data you have in source, there is no need to create another table to display it the way you have mentioned. Just create a pivot table as done in attached example.

          Hope this helps.

          Thanks

          Amit

            • Dynamically load numerous tables by a dimension

              Hi Amit,

              thanks for your hint.

              Apparently a pivot table does not provide the needed functionality. It displays the allocation, but does not provice the mentioned possibility to filter by each dimension AND its status.
              Once selecting "Yes" for the status in the pivot, all entries (no matter the product group) are reduced to status "Yes".
              After this it is not possibile to look at the entries with Status "No" of another (specific) product group.

              Therefore I thought I might be clever to split this up into several tables.

              Martin

                • Dynamically load numerous tables by a dimension
                  John Witherspoon

                  There's no need to split it into several tables. You seem to be describing an "inverse crosstable load". I don't believe there's any direct support for that like there is for a crosstable load, but you can do it like this:

                  RawData:
                  LOAD * INLINE [
                  Customer, ProductGroup, Status
                  0001, Screws, Yes
                  0001, Nails, No
                  0001, Glue, Yes
                  0002, Screws, Yes
                  0002, Nails, No
                  0002, Glue, Yes
                  0003, Screws, No
                  0003, Nails, No
                  0003, Glue, No
                  ];
                  ProductGroups:
                  LOAD fieldvalue('ProductGroup',iterno()) as ProductGroup
                  AUTOGENERATE 1
                  WHILE len(fieldvalue('ProductGroup',iterno()))
                  ;
                  LET PG = peek('ProductGroup',0,'ProductGroups')
                  ;
                  CustomerPurchases:
                  LOAD Customer, Status as $(PG)Status
                  RESIDENT RawData
                  WHERE ProductGroup = '$(PG)'
                  ;
                  FOR row = 1 TO noofrows('ProductGroups') - 1
                  LET PG = peek('ProductGroup',row,'ProductGroups')
                  ;
                  LEFT JOIN (CustomerPurchases)
                  LOAD Customer, Status as $(PG)Status
                  RESIDENT RawData
                  WHERE ProductGroup = '$(PG)'
                  ;
                  NEXT

                  DROP TABLES
                  RawData
                  ,ProductGroups
                  ;

                  See attached for a working example using this script. Note that while you could generate the ProductGroups more simply like this...

                  ProductGroups:
                  LOAD DISTINCT ProductGroup
                  RESIDENT RawData
                  ;

                  ...the original script is much more efficient when dealing with large volumes of data.

                  Please let me know if I've misunderstood your requirement.