You can try with a Qualify before the load sentence, what this do is concat the table name to the field names.
See the next example
LOAD * INLINE [
Customer, Sales, OtherField
1, a, x
2, b, y
3, c, z
LOAD * INLINE [
Customer, Quote, OtherField
1, h, k
2, j, l
3, n, m
you will get a model like this. I use in line loads, but you can replace it for your data source.
Hope it helps!
Not sure if it would work how I need as my 2 data extracts have different columns and different data and need to be analysed differently, If I was to concat it first would it only pull through the columns that match? (all of the ones that do match I have ensured are titled the same)
Here are my data fields, Some match and some don't,
Quote Data Sales Data Brand RegNo Department Sale Date RegNo Manufacturer Manufacturer Model Description Model Description Colour Colour Department Sale Mileage Brand Customer Early Termination? Hirer Total Sales amount ContractTermDate Sale Amount Last Quoted Date RFL AGE Warranty Sales No of Quotes Net Sales price CAP Sale amount + warranty CAP % Sale Mileage Quote Year Desirable Specification QTR of Quote Priced as an Exception Quote Month Desirable Colour Mileage >100k CAR/LCV QuotedAmount VATStatus Total Sales amount CAP RFL CAP % Warranty Sales Net CAP % Warranty Type Sale Year Sale Date QTR of Sale CAR/LCV Sale Month VATStatus Mileage >100k Desirable Specification Warranty Type Priced as an Exception ContractTermDate Buyer profile AGE Buyer No of Quotes Sales Person Last Quoted Date Quote Age statusid Sales Person Customer Buyer Buyer profile statusid
Concatonate would create a table with all distinct column names - meaning it would not eliminate columns. You just want to make sure that any columns that should be considered the same are named exactly the same (case counts).
If your tables have many rows, I would consider creating placeholders in each table for columns that don't have a match in the other.
Null() as A,
Null() as B,
X, Y, Z
A,B,X,Y, Null() as Z
This is particularly important in a multi-tier load strategy where your first tier creates QVD and subsequent tiers consume them. You will want matching columns to maintain an optimized load.
If your tables do not have too many rows, this may not be an issue, though with a concatenated facts table, I would assume it would eventually require this.