Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Combining Multiple Tables with Similar Fields

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.

1 Reply
pradosh_thakur
Honored Contributor II

Re: Combining Multiple Tables with Similar Fields

Hi

These  ways you can do

1Smiley Very Happyid you try apply map?

2:did you try left join?

Let me know if you need more help or this doesn't work.

regards

Pradosh

Learning never stops.