6 Replies Latest reply: Aug 17, 2015 8:11 AM by Виталий Чуприна RSS

    Store data

    Виталий Чуприна

      Hi guys,

       

      I receive files with data in different sheet, see below name of columns in the first sheet

       

      ModelCodeColorSizeSalesPrice

       

      and second

       

      ModelCodeColorSales STDPrice

       

      They have four columns with the same name.

       

      Could you please advise how can I load data in one output.

        • Re: Store data
          Srikanth P

          The best approach is talk to source of file creator why they generated the filed names differently. Try to correct field names discrepancy on their end.

           

          Otherwise, you have manually create Field Names Mapping table & use the Rename Fields using Mapping method.

           

          The Field Names mapping should be maintained by you manually or Look for other options how to automate the creation of Field names mapping.

          • Re: Store data
            Виталий Чуприна

            Thank you for response.

             

            In output I want to see following columns with data.

            Model       Code       Color       Size       Sales       Sales STD      Price

             

            So is this possible to put data in one output if tables have different columns number and different data in columns, for example: first sheet doesn't contain 'Size' column, but has 'Sales STD' and second sheet conversely.

              • Re: Store data
                Srikanth P

                Are trying to join the these data files ?

                • Re: Store data
                  Srikanth P

                  Please provide the sample data & desired o/p so community can help you.

                    • Re: Store data
                      Виталий Чуприна

                      Ok. As i said before data comes in one file. Usually files have two sheets one with Last Week data (Sheet 1) and one with STD data (Sheet 2).

                      So I should create third table that will be contains all columns?

                       

                      See my script below:

                       

                      IF( WildMatch(Upper(vSheetName), '*Sheet2*') AND NOT WildMatch(vSheetName,'*WEEK*','*database*', '*_xlnm*') ) THEN

                       

                      DataLW:

                      LOAD

                      FileName() as FILE_NAME,

                           '$(vSheetName)' as SHEET_NAME,

                           Date(SubField('$(vWKEND)',':',2),'YYYY.MM.DD') AS WEEK_ENDING_DATE,

                           SubField('$(vSeason)',':',-1) AS SEASON,

                           Model AS STYLE,

                           Colour AS COLOR_DESC,

                           Size AS SIZE,

                           [Sales, pcs] AS SLS_U,

                           [Price, €] AS CURRENT_PRICE

                      FROM

                      [$(vFile)]

                      (biff, embedded labels, header is $(vRowStart) lines, table is [$(vSheetName)$])

                      Where Size <> ''

                      ;

                      ELSE


                      DataSTD:

                      LOAD

                      FileName() as FILE_NAME,

                           '$(vSheetName)' as SHEET_NAME,

                           Date(SubField('$(vWKEND)',':',2),'YYYY.MM.DD') AS WEEK_ENDING_DATE,

                           SubField('$(vSeason)',':',-1) AS SEASON,

                           Model AS STYLE,

                           Colour AS COLOR_DESC,

                           pcs AS SHIPMENTS_U,

                           pcs1 AS STD_SLS_U,

                           [€] AS CURRENT_PRICE

                       

                       

                      FROM

                      [$(vFile)]

                      (biff, embedded labels, header is $(vRowStart) lines, table is [$(vSheetName)$])

                      Where IsNum(€)

                      ;

                       

                      ENDIF

                      ENDIF


                      NEXT

                      Drop Table Temp_Tables;

                      NEXT

                      STORE ....

                  • Re: Store data
                    Borja Rodriguez

                    I'd recommend keeping "Code" in both tables and don't load "Model" and "Color" from one of the tables because if my guess is right, it is redundant data.

                     

                    1st table:

                    ModelCodeColorSizeSalesPrice

                     

                    2nd table:

                     

                    ModelCodeColorSales STDPrice

                     

                    The field "Code" will link the data from both tables.