1 Reply Latest reply: Oct 6, 2017 2:51 PM by pradosh thakur RSS

    Combining Multiple Tables with Similar Fields

    Zach Umsted

      Hi,

       

      I'm having difficulty combining multiple tables together and having them match up properly.

       

      I have 8 data loads pulling from SharePoint lists with 20 data points, all with the same source names of the fields and renamed to the same names. Example (not all of the fields):

      LOAD

           [OIDÂ Â ]) as OID,

           [Promotion_Level  ] as [Promotion Level],

           [Form_Number  ] as [Form Number],

           Timestamp#([Edition_Date  ], 'MM/DD/YYYY') as [Edition Date],

           [Owner_Name  ] as [Form Owner],

           [Law_Department_Number  ] as [Law Dept Number],

           Timestamp#([Law_Dept_Expiration_Date  ], 'MM/DD/YYYY') as [Law Dept Expiration Date],

           [Title  ] as Title

           ...

       

      I have 6 data loads pulling from Excel files with 7 data points, all with the same source names of the fields and renamed to the same names. Example:

      LOAD

           [Form ID] as OID,

           Rebranding,

           [Rebranding Exception],

           Modernization,

           [Modernization Exception],

           Details as [Transformation Details]

           ...

       

      The SharePoint feeds contain all of the unique OID numbers, and all of the Excel files reference OIDs that correspond to OIDs provided by the SharePoint feeds, though not every OID is represented in the Excel files.

       

      What I want to do, is have the additional fields provided by the Excel files appended to the existing OIDs provided by the SharePoint feeds. If there isn't an OID represented in the Excel files, I want the fields to just be Null.

       

      But, every time I've tried loading, it just creates a separate instance of the OID for the Excel file loads, with the information provided by the SharePoint fields Null, and the SharePoint OID has all of the Excel file loaded information as Null.

       

      Example of a resulting Straight Table:

      OIDPromotion LevelForm NumberEdition DateForm OwnerLaw Dept NumberLaw Dept Expiration DateTitleRebrandingRebranding ExceptionModernizationModernization ExceptionTransformation Details
      5000ActiveFORM50001/1/2011John DoeXXXXX1/1/2018This is a Form-----
      5000-FORM5000-----YNo exceptionNBecauseTransformed 5/11/2011

       

      This is throwing off the inventory count that is provided, and also makes it difficult to properly review the state of a form.

       

      I've attempted to use Join, Keep, and Concatenate, with little effect. Not saying they won't work, just that I may be misunderstanding how to use them.

       

      I have QlikView 11.

       

      Thanks for any help you can provide.