3 Replies Latest reply: Jul 31, 2014 5:30 PM by Josh Good RSS

    Load Column From Excel without some values

      Hello everybody

       

      I have a problem with a load script

       

      I have a fact table with all the information and I need to add a colunm from a Excel File. The problem is that some of the data in the table not apear in the excel File. Example

       

      Fact table (prior Join)

      ITEM

      00001

      00002

      00003    

      00004

      00005

       

      Excel

      ITEM          ITEM_KEY

      00001          101001   

      00004          101004

      00005          101005

       

      Fact table (With a simple Join)

      ITEM          ITEM_KEY

      00001          101001

      00002          -

      00003          -   

      00004          101004

      00005          101005

       

      What I need is, if the value item is in the exel (relation table) the field in ITEM_KEY needs to be equal to the value in the excel, but if the value is not in the Excel the Load need to pass the value of the ITEM column. The result I need for the load is the next:

       

      ITEM           ITEM_KEY

      00001          101001 (This value is from the Excel)

      00002           00002 (This value is from the Item column form the same fact table)

      00003           00003 (This value is from the Item column form the same fact table)

      00004          101004 (This value is from the Excel)

      00005          101005 (This value is from the Excel)

       

      Can anyone could help me

        • Re: Load Column From Excel without some values
          Josh Good

          You could use a mapping to do this.  The script below is an example.  Note I used inline loads to get the table in so the script would self contain in the example.

           

          -Josh

           

          FactTable:

          Load * Inline [

          ITEM

          00001

          00002

          00003

          00004

          00005

          ];

           

          Excel:

          Mapping Load * Inline [

          ITEM, ITEM_KEY

          00001, 101001  

          00004, 101004

          00005, 101005

          ];

           

          NewFactTable:

          Load

              ITEM,

              ApplyMap('Excel',ITEM, ITEM) as ITEM_KEY

          Resident FactTable;

           

          Drop Table FactTable;

            • Re: Load Column From Excel without some values

              Thanks

               

              Only one more question..

               

              What happen if in the original Fact Table I have like 30 Others columns, I van create this map in form of Join or do you think is better to create another fact table??

                • Re: Load Column From Excel without some values
                  Josh Good

                  My example didn't include the other fields, just included other fields.  Upon reflection I thought I should clarify that you don't need to load the fact table first and then use a resident load, I just did that because I was using an inline load.

                   

                  Your final script would look more like this:

                   

                   

                  Excel:

                  Mapping Load * Inline [

                      ITEM,

                      ITEM_KEY

                  FROM 'lib://Folder/ExcelFileName.xlsx'

                  (ooxml, embedded labels, table is Sheet1);

                   

                  FactTable:

                  Load

                      ITEM,

                      Field1,

                      Feild2,

                      ....,

                      ApplyMap('Excel',ITEM, ITEM) as ITEM_KEY

                  From SQL Select * From FactTable;