I am concatenating two tables - sales orders and sales quota - into one fact table. Seemed to work fine, until I noticed that selecting a salesperson didn't show the quota amounts. So I created a quick table box over the fact table, and noticed that in records that came from orders, the sales person ID was right justified, and in records where it came form the quota table, the ID was left justified. Looking at the table box properties, this seems to indicate it sees the quoata sales person IDs as strings, and that would be why it can't relate to the IDs in the sales person dimensional table (where they are numbers).
Easy fix, I thought! I just wrapped Num() around the ID in the part where I cocatenate, a la:
CONCATENATE
LOAD QuotaDate As OrderDate, //Change to match sales order field
Num(QuotaSalesPerson) As EmployeeBusinessEntityID, //Change to match sales order field
QuotaQuarterly,
QuotaMonthly
RESIDENT SalesQuota;
Unfortunately, when looking at the table box after a reload, the Num function appears to have just made them all NULL (there is - in the field). The values are indeed numbers, so I don't know why it would fail to resolve them as such.
The initial SalesQuota table is generated from an Excel crosstable load - the sales person IDs were column headers in the spreadsheet instead of values in a field. Maybe this has something to do with it? This is the initial SalesQuota load:
SalesQuota:
CrossTable(QuotaSalesPerson, QuotaAmount)
LOAD F1 as QuotaDate,
[275],
[276],
[277],
[279],
[280],
[282],
[284],
[286],
[288],
[289],
[290],
Website As [0] // Change this to be an ID value
FROM
(ooxml, embedded labels, table is Quota);
