Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Linking Multiple XLS Data from External Sources

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.

1 Reply
Not applicable
Author

Bump?!  Not sure about the protocol on bumping a thread around here...