Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Re: Help with generic keys with 2 facts

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.

5 Replies
sinanozdemir
Valued Contributor III

Re: Help with generic keys with 2 facts

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

Re: Help with generic keys with 2 facts

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

sinanozdemir
Valued Contributor III

Re: Help with generic keys with 2 facts

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

Re: Help with generic keys with 2 facts

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
Valued Contributor III

Re: Help with generic keys with 2 facts

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

Community Browser