1 Reply Latest reply on Dec 1, 2018 6:20 PM by Fabian König

    Time constraints in join or blend

    Fabian König

      Hi there!


      Let's say I have 3 tables:

      - invoices

      - customers

      - customer groups


      plus a m-to-n relationship table called "LINK", which captures

      - which customer

      - belongs to which group

      - from which date

      - until which date (or NULL if there is no end date)


      I now want to have a revenue break-down based on the customer groups to which the invoiced customer was assigned at the time when the invoice was created.




      Invoice NumberInvoice DateInvoiced Customer IDRevenue
      1001Jan 1stJim100
      1002Feb 2ndTom500
      1003March 3rdJim50
      1004March 15thTom


      1005April 4thJim75



      LINK IdCustomerGroupFromUntil
      1JimAJan 1stFeb 28th
      2JimBMarch 1stNULL
      3TomAMarch 1stNULL


      So we have a couple challenges:


      • Jim belongs to group A when Invoice 1001 is issued.
      • Jim belongs to group B when invoices 1003 and 1005 are issued.
      • Tom doesn't belong to any group yet when his invoice 1002 was issued.
      • Tom belongs to Group A when his invoice 1004 is issued.


      Ultimately I would like to see a crosstab as follow:





      Formatting the crosstab is not so much the issue, but how can I get the Group assignments for Jim and Tom based on the invoice date ?


      Would I rather JOIN or BLEND this (since the other tables which are also needed in real-life make things quite complex already and double counting is an issue, I'd rather BLEND than JOIN).


      How would I go about linking the blended data based on the date constraint ?


      Thank you & Best Regards


        • 1. Re: Time constraints in join or blend
          Fabian König

          So I got a step further, thanks also to Sofya!


          Data prep:

          • create Left Joins:

            [invoices] LEFT JOIN [customers] LEFT JOIN [link] LEFT JOIN [customer groups]

          • create a calculation:

            [ValidAssignment] = [Invoice Date] >= [From] AND IF [Until] <> null THEN [Invoice Date] < [From] ELSE true END


          Use the [ValidAssignment] calculated field as a filter


          And there you go: group associations are linked to invoices based on when the groups have been assigned and the invoice date.