Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help with generic keys with 2 facts

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?

1 Solution

Accepted Solutions
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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.

View solution in original post

5 Replies
sinanozdemir
Specialist III
Specialist III

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

Not applicable
Author

the problem is, I can't get the date of the targets together with the other date table from Fact1

sinanozdemir
Specialist III
Specialist III

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

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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.

sinanozdemir
Specialist III
Specialist III

I missed the last part of the script. I think your solution should do the trick.