Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi guys,
I have following tables:
Fact1:
Load
DocNoID,
Amount,
SalespersonID,
Date
Resident All;
Fact2:
Load
DocNoID,
CustomerID,
EndcustomerID
Resident Sales;
Customer:
Load CustomerID, Customername, CustomerCountry
Resident Customer.qvd (qvd);
Endcustomer:
Load EndcustomerID, Endcustomername, EndcustomerCountry
Resident Endcustomer.qvd (qvd);
TargetsByYear:
SalespersonID,
Date,
TargetAmount
FROM XY;
which way is right to make generic keys?
Change the load of Facts1 into something like:
Fact1:
Load
DocNoID,
Amount,
SalespersonID,
Date,
SalesPersonID & '|' & YearStart(Date) AS TargetDate
Resident All;
and the last load of TargetsByYear into something like:
TargetsByYear:
// SalespersonID, // Normalized. Which table has more SalesPersonIDs?
SalesPersonID & '|' & YearStart(Date) AS TargetDate,
TargetAmount
FROM XY;
Does this do the job?
Peter
[Edit] Modified the code so that you can get Year Targets by SalesPerson. TargetDate has become a real key that includes the year and the SalesPerson. Just a guess of what you are up to.
I am going to be lazy and say how about dividing Fact2 table into two tables - one is for CustomerID and the other one is for EndcustomerID:
Fact2:
LOAD
DocNoID,
CustomerID
Resident Sales;
Fact3:
LOAD
DocNoID
EndcustomerID
Resident Sales;
Hope this helps
the problem is, I can't get the date of the targets together with the other date table from Fact1
It seems like the Fact1 table is related to Fact2 table with DocNoID and if you are saying that you cannot get the dates, then it sounds like not all DocNoIDs have dates.
You can post a simple data which could make it easier to understand the issue.
Thanks
Change the load of Facts1 into something like:
Fact1:
Load
DocNoID,
Amount,
SalespersonID,
Date,
SalesPersonID & '|' & YearStart(Date) AS TargetDate
Resident All;
and the last load of TargetsByYear into something like:
TargetsByYear:
// SalespersonID, // Normalized. Which table has more SalesPersonIDs?
SalesPersonID & '|' & YearStart(Date) AS TargetDate,
TargetAmount
FROM XY;
Does this do the job?
Peter
[Edit] Modified the code so that you can get Year Targets by SalesPerson. TargetDate has become a real key that includes the year and the SalesPerson. Just a guess of what you are up to.
I missed the last part of the script. I think your solution should do the trick.