3 Replies Latest reply: Feb 8, 2016 8:26 AM by Bob Schmidt RSS

    Create static columns from dynamic data

    Bob Schmidt

      I apologize if this has been answered but I have searched many places and maybe I just don't understand what I am reading.  My data looks something like:

      Plant     Product   02012016   02022016   02032016  ...

      Atlanta   Cars             52               77            23          ...

      Dallas     Cars             15              27             56         ...

       

      Every day the date in the column changes.   I need to load in such a way that a straight table can be loaded so the now different column names load into the same relative column - oldest first.  It appears to me I need to do something like add a static name into the new data and use that in the chart referring to the date as the text for the column.   I hope that makes sense.

       

      Thank you - Bob Schmidt

        • Re: Create static columns from dynamic data
          Marcus Sommer

          You neet to transform your crosstable to a normal table-structure, see: The Crosstable Load.

           

          - Marcus

          • Re: Create static columns from dynamic data
            Massimo Grossi

            something like

             

            S:

            load * inline [

            Plant     Product   02012016   02022016   02032016 

            Atlanta   Cars             52               77            23         

            Dallas     Cars             15              27             56        

            Dallas     Bike             15              27             56        

            ] (delimiter is spaces);

             

            T: CrossTable (MonthText, Val, 2) LOAD  *  Resident S;

             

            Final: Load Plant, Product, Date(Date#(MonthText,'MMDDYYYY'), 'YYYY MM') as Month, Val Resident T;

              

            DROP Table S, T;

             

              • Re: Create static columns from dynamic data
                Bob Schmidt

                Sorry for my inability to communicate - I have the data in a text file downloaded from SAP and load that into a straight table during an automated hourly update.   I have tried this with a pivot table but I cannot find any way to sort like this so I believe I am stuck with a straight table.

                I tried loading this into a straight table as shown initially [column names PLANT, PRODUCT, 02012016, 02022015, etc.] then sorted by the first date (02012016) column in descending sequence, then the next date column in descending sequence, etc.  This is a published application that needs to contain the final table.  It is not one created by the users.  I need the new "first date" data to be in the same relative column as the previous "first date" so it can be sorted as indicated.

                I think I need to load the data with a reference field (maybe something like DATE_FIELD_01 through DATE_FIELD_XX that is linked somehow to the first date column, second date column, etc.).  I then need to show the value of the data as the text for the column.

                I am sorry I am so much trouble putting this into words.