3 Replies Latest reply: Mar 26, 2018 12:21 PM by David Forest RSS

    Associating tables on time and possibly a second atribute

    Joe Muente

      I'm working on a data model for an app which will compare 2 sets of data, the first of which is a list of invoices with details, and the second is a list of customer complaints with complaint related details.  At the highest level we just want to compare these lists on time, and be able to see per period (month in this case), how many complaints were registered versus invoices.  I've joined the tables on time, and everything works perfect for the high level metrics we are looking for.

       

      Tables can also kind of be associated by invoice number, however not all complaints will have an invoice number assigned, which is causing issues.  If I actually make the association on both invoice number and time, then we lose any complaints in a period that don't have an invoice number assigned, and that is not what we want to have happen.

       

      Is there a way to associate these tables on time, but also allow a filter to be applied to dimensions on the invoice table that will then affect the complaints table based on connecting on invoice number?  I think my problem is that not all complaints have an invoice assigned, but we still want to include the ones that don't when comparing time periods, just not when we've filtered down to invoice dimensions.

        • Re: Associating tables on time and possibly a second atribute
          David Forest

          use a set expression to ignore invoice selection in your complaints metric

          e.g.

          Sum({$<InvoiceID=>} ComplaintID)

            • Re: Associating tables on time and possibly a second atribute
              Joe Muente

              Thanks for the advice, but I'm either not sure how to apply it, or possibly I may have explained my goal/problem incorrectly.  I have a table using Year/Month as the dimension.  When I had the data model associating the two tables just on date I had been seeing what I considered to be accurate counts of complaints for each month.  When I modified the model to join on invoice# and date (using a synthetic key) these numbers dropped pretty significantly for each month, but created a row that had a blank period where the "missing" complaints went.  I assume this had to do with complaints that had no invoice number affecting the join.

               

              In the new model associated on both fields, my count of complaint numbers had been:

               

              Count(distinct [Case Number])

               

              And I modified to this to attempt to apply set analysis:

               

              Count({$<[Billing document-Billing document-Invoice Num]=>} distinct [Case Number])

               

              The counts didn't change however.  Not sure if I made a mistake in my formula, or it is something else.

               

              Also, I wasn't sure how the set analysis formula would work as we started applying filters.  What I want it to do is when no filters are applied to include counts of all invoice lines and all complaints from both tables, regardless of if they have invoice number in common, but then if filters are applied they limit the results to just records which do have invoice number in common.  I'm worried that if the set analysis ignores invoice number, then if a filter is placed on the invoice table, it will affect only that table, but not the complaints, so we'd be then comparing a filtered list of invoices to all complaints, instead of just the complaints relevant to the invoice selection.

               

              Thanks again,

               

              Joe

            • Re: Associating tables on time and possibly a second atribute
              David Forest

              Without sample data hard to say what is going on exactly, syntax looks correct if that is indeed the field name.

              Once the data is loaded into click, you need to stop thinking about the data as being in separate tables because it gets transformed into a cube.

              All filtering in Qlik filters all the data, you are never really just filtering a table, so the second concern shouldn't be a problem. If the counts after any filter applied need to only contain Invoices with a complaint, it may be prudent to set a flag in the load, just add a field to the "complaint" table  1 as HasComplaint. you would then have to create an if statement for every measure to set this into your set expression. e,g.

              If(len(GetCurrentSelections())>0, Count({$<HasComplaint=1>} [Case Number]),Count({$<HasComplaint=>} [Case Number]))