7 Replies Latest reply: Jun 14, 2015 6:36 PM by Petter Skjolden RSS

    Script Load Content as Header

    Robert Reitschuster

      Dear all,

      I have tried to find a solution for this problem.
      Transforming content into a different format.

       

      material_noATTRIBUTEVALUEATTRIBUTE
      1saleschannelNonElectronics
      1salesregionASIA
      1assortmentBags
      2saleschannelComputer
      2salesregionEMEA
      2assortmentKeyboard
      3saleschannelNonElectronics
      3salesregionall
      3assortmentCleaning

       

      Result

       

      material_nosaleschannelsalesregionassortment
      1NonElectronicsASIABags
      2ComputerEMEAKeyboard
      3NonElectronicsallCleaning

       

      I had not success with the crosstable load or similar wizard options.

      Thank you for any support and ideas
      Robert

        • Re: Script Load Content as Header
          Federico Velasco

          Hi,

           

          Try this:

           

          Data:

          LOAD

          *

          Inline [

          material_no, ATTRIBUTEVALUE, ATTRIBUTE

          1, saleschannel, NonElectronics

          1, salesregion, ASIA

          1, assortment, Bags

          2, saleschannel, Computer

          2, salesregion, EMEA

          2, assortment, Keyboard

          3, saleschannel, NonElectronics

          3, salesregion, all

          3, assortment, Cleaning

          ];

           

          NoConcatenate

          ATTRIBUTEVALUE_TABLE:

          LOAD Distinct

            ATTRIBUTEVALUE

          Resident Data;

           

          LET sFields = NoOfRows('ATTRIBUTEVALUE_TABLE')-1;

           

          FOR i = 0 to $(sFields);

           

            LET sFieldName = Peek('ATTRIBUTEVALUE', $(i), 'ATTRIBUTEVALUE_TABLE');

           

            TEMP1:

            LOAD

            material_no,

            ATTRIBUTE as $(sFieldName)

            Resident Data

            Where ATTRIBUTEVALUE = '$(sFieldName)';

          NEXT i;

           

          DROP Table Data;

          DROP Table ATTRIBUTEVALUE_TABLE;

          • Re: Script Load Content as Header
            Petter Skjolden

            The very simplest approach is as follows:

             

            LOAD
              material_no,
              MaxString(saleschannel) AS saleschannel,
              MaxString(salesregion) AS salesregion,
              MaxString(assortment) AS assortment
            GROUP BY
              material_no;
            LOAD
              material_no,
              If( ATTRIBUTEVALUE = 'saleschannel' , ATTRIBUTE ) AS saleschannel,
              If( ATTRIBUTEVALUE = 'salesregion' , ATTRIBUTE ) AS salesregion,
              If( ATTRIBUTEVALUE = 'assortment' , ATTRIBUTE ) AS assortment
            ;
            LOAD * INLINE [
            material_no ATTRIBUTEVALUE ATTRIBUTE
            1 saleschannel NonElectronics
            1 salesregion ASIA
            1 assortment Bags
            2 saleschannel Computer
            2 salesregion EMEA
            2 assortment Keyboard
            3 saleschannel NonElectronics
            3 salesregion all
            3 assortment Cleaning
            ] (delimiter is \t);
            //
            
            

             

            What you put from line 14-25 could be some other LOAD statement as long as it contains the necessary columns from a spreadsheet or a SQL database or some other source.

              • Re: Script Load Content as Header
                Stefan Wühl

                I believe the very simplest approach is like

                The Generic Load

                 

                Data:

                GENERIC LOAD

                *

                Inline [

                material_no, ATTRIBUTEVALUE, ATTRIBUTE

                1, saleschannel, NonElectronics

                1, salesregion, ASIA

                1, assortment, Bags

                2, saleschannel, Computer

                2, salesregion, EMEA

                2, assortment, Keyboard

                3, saleschannel, NonElectronics

                3, salesregion, all

                3, assortment, Cleaning

                ];

                 

                In Henric's blog is also described how to combine the tables into one again, and why he believes you shouldn't do that.

                  • Re: Script Load Content as Header
                    Petter Skjolden

                    He asked for a single table ... so ...

                     

                    Furthermore it depends what the real data is - make note of what HIC says in his article:

                     

                         The result is one big table that contains all attributes; a table that often is sparse

                     

                    This does not look like a typical sparse table case - unless the sample data is very simplified. I definitely think

                    Generic Load is excellent with many attributes and sparse data.

                      • Re: Script Load Content as Header
                        Stefan Wühl

                        Sure, I don't mind to re-combine the tables back into a single one. The recipe is there.

                         

                        A big advantage of a generic load over your approach is that you don't need to hardcode the values you expect in ATTRIBUTEVALUE. It's dynamic.

                          • Re: Script Load Content as Header
                            Petter Skjolden

                            That is true. Just to be clear what I meant is that by saying "simplest" I really meant simplistic - and not flexible as the Generic Load certainly is. I still think that Generic Load is not the ultimate solution that should be used in every case where you need an unpivot. One disadvantage is that you get multiple tables that might clutter the clarity of your data model for business users.