1 Reply Latest reply: Jun 22, 2017 12:21 PM by Jason Graf RSS

    Linking Multiple XLS Data from External Sources

    Jason Graf

      Been using Qlik Desktop for a couple of years, first time asking a question of the community because I'm wondering if there is a better way to do what I'm already doing.  Here is the situation.....

       

       

      I have multiple excel data files that come from multiple sources (different business partners), and thus generally in very different formats.  Each of these contain similar information, but not necessarily distinct.  For example; One file might contain "AccountID"s for each of that partner's customer accounts, and the other might contain "AcctID" for each of that partner's customer accounts, and they might have similar account numbers for different customers since they are from different sources.  The files overall will contain these account id's, along with things like the product they use, a related third party identifier, the amount of assets in the account, etc.  On my end, we are trying to aggregate the data from each of these partners, so that we can get an overall view of the business - total assets, total accounts, total assets with each third party, etc. I get these files from our partners, Monthly.  To make matters worse, one of the partners gives me two files which I have to aggregate beforehand... I use QlikView for the aggregation to create another xls file which is then loaded by QlikSense.  Yea...

       

      Up until this point, the way I put this together was by pulling each of these files into QlikSense (after merging the two files from a single partner in QlikView and exporting it), and as the data comes in I rename each of the datapoints to a distinct name, unless it is something I want to aggregate (like AccountID).  I turn each of the AccountID's into a unique identifier by merging them with another similar column on the way in (ie:  AcctID&'|'&ProdID), of which there will never be a duplicate.  Then I send all the important columns to a Compiled Table, and drop the duplicate fields from the original tables... essentially leaving me with a compiled table of the important joined fields, and then the remaining data on the source tables which reference back to the AcctID&'|'&ProdID code I created.  Finally, the compiled table also reads extra info from various fields off of all the different reference tables.

       

      While this method has proven effective, it is also fairly onerous to compile the data every month because, every single time, I have to reformat the different partner tables so that they can even be loaded into QlikSense.  I'm wondering if there is instead a way to load each of these tables in, as they come from the partners, without having to first manipulate them for use.   It would save me a few hours to say the least...

       

      Any thoughts?  Below is an example of what I'm working with using, lets say, 4 partners...

       

      Partner 1 Table 1

      Acct#AcctNameProdIDAssets
      1Data Maker, Co.RML$157
      2Coders for Higher EducationRML$35
      3University of ScriptingQPL$984
      4Technical Institution of MetricsLTY$579

       

      Partner 2 Table 1

      AccountCurrent $Formal NameProduct
      142  67$91Shoes R Us, Inc.QQR
      142  68$842We Need Shoes, Co.QQR
      142  69$736Laces Are In, Inc.QQR
      142  70$721Laces Out, LLCQQR

       

      Partner 3 Table 1

      Acct Full NameAcct CodeProductValue
      Cherry Company1AFD$29
      Apple Company4AFD$753
      Plum Company5DFM$64
      Grape Company6AFD$284

       

      Partner 4 Table 1

      ID#Formal Customer NameProd.ID#Customer State
      863Jelly Company IncTYYCT
      864Jam & Fresh CompanyYTMIA
      865Peanuts with Butter on Bread LLCTYYNY
      868Bread with no Butter Inc.TYYUT

       

      Partner 4 Table 2

      Informal NameID#Current ValueShares
      RT & P Inc861$1235
      J&F Co.864$1465
      Bw/oB,Inc.868$6348
      A&P Inc870$4824

       

      Ref Table 1

      PRODUCT IDSProduct Inception
      TYY7/13/09
      DFM12/14/03
      QQR1/26/08
      YTM5/4/13
      RML2/27/07
      AFD2/26/17
      QPL11/4/06
      LTY1/11/11

       

       

      Super confidential information; can't provide a direct example from the files.