topic Re: Help with generic keys with 2 facts in QlikView

Help with generic keys with 2 facts

Thu, 02 Jul 2015 12:37:11 GMT

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?

Re: Help with generic keys with 2 facts

sinanozdemir — Thu, 02 Jul 2015 13:20:07 GMT

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

Re: Help with generic keys with 2 facts

Thu, 02 Jul 2015 13:33:50 GMT

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

Re: Help with generic keys with 2 facts

sinanozdemir — Thu, 02 Jul 2015 13:39:43 GMT

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

Peter_Cammaert — Thu, 02 Jul 2015 13:41:12 GMT

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.

Re: Help with generic keys with 2 facts

sinanozdemir — Thu, 02 Jul 2015 13:44:14 GMT

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