topic Re: Link Table & Dates Help in QlikView

Link Table & Dates Help

stuwannop — Tue, 12 Mar 2013 16:41:40 GMT

Hi Everyone

Hope I can explain this properly. I have a sales order table, invoicing table and contracts table. All of them have a few dimensions in common which are Product, Region and Salesperson. I also wrote a master calendar for the dates.

I put all of these into a link table but the Contracts table is not date dependent, i.e. it doesn't have a date dimension. Now, predictably, if I select a date (e.g. 2013) I can see the sales orders and invoicing but I cannot see the sum of contracts on another tab (as it does not have a "date"). I want to be able to see a sum of the contracts table regardless of what date dimensions I use.

This would get me to a point where I could see YTD sales, YTD Invoicing and a total contract base.

Am I trying to do the impossible or is there a way around this?

Thanks
Stu

Re: Link Table & Dates Help

Tue, 12 Mar 2013 16:57:27 GMT

I can think of one solution, What you can do is create a ID field in contracts table and put that Id field in Link table. this way you are linking Contracts table and Link table. Now when you make selections the associated contracts records will be selected.

Re: Link Table & Dates Help

Nicole-Smith — Tue, 12 Mar 2013 17:01:21 GMT

You can ignore the date selection using set analysis:

sum({<Date = >}Sales)

Re: Link Table & Dates Help

manojkvrajan — Tue, 12 Mar 2013 17:55:43 GMT

Stuart, We can definitely look for workaround to link all these 3 tables using the date field. Can you share the qvw and sample data set?

Re: Link Table & Dates Help

stuwannop — Wed, 13 Mar 2013 15:02:06 GMT

Thanks for the suggestions, but it still doesn't seem to be working. I'm afraid I don't really have any sample data I can use as its quite report that took a long time to build. I've attached a screenshot of my schema does this help any? I've also pasted the script of the Lync table below.

LinkTable:

Load

CRMCustomers.accountid,

CRMCustomers.CUSRegion as Region,

CRMCustomers.accountno as Account,

CRMCustomers.AccountManagerProper as EmployeeName

Resident CRMCustomers;

//Take the OrderID and the dates from the next table, concatenate to table above.

Concatenate

Load

ORDID,

ORDSilo as Silo,

ORDGroup as Channel,

ORDCustomerNo as Account,

ORDDate as Date,

ORDRegionName as Region,

ORDSalesADAMProper as EmployeeName

Resident OrdersIn;

Concatenate

Load

Pipeline.opportunityid,

Pipeline.OPPSilo as Silo,

Pipeline.OPPCloseDate as Date,

Pipeline.OPPRegion as Region

//     OPPAccountManagerProper                              as EmployeeName

Resident Pipeline;

Concatenate

Load

CH.id,

CUS.accountnumber as Account,

CUS.Region as Region,

PR.Silo as Silo,

CUS.AccountManagerProper as EmployeeName

Resident CH;

Concatenate

Load

EMP.EmployeeID,

    EMP.Name as EmployeeName

Resident EMP;

Re: Link Table & Dates Help

stuwannop — Wed, 13 Mar 2013 15:06:32 GMT

Thanks Nicole this is helpful - I have several date fields (Date_FY, Date_Month etc.) What is the Syntax for excluding more than one date selection?

Thanks

Re: Link Table & Dates Help

Nicole-Smith — Wed, 13 Mar 2013 16:29:32 GMT

sum({<Date_FY = , Date_Month = >} Sales)

Re: Link Table & Dates Help

stuwannop — Wed, 13 Mar 2013 16:32:29 GMT

Spot on that!

Thanks for your help, really useful trick to know for the future also.

Stu