Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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# | AcctName | ProdID | Assets |
---|---|---|---|
1 | Data Maker, Co. | RML | $157 |
2 | Coders for Higher Education | RML | $35 |
3 | University of Scripting | QPL | $984 |
4 | Technical Institution of Metrics | LTY | $579 |
Partner 2 Table 1
Account | Current $ | Formal Name | Product |
---|---|---|---|
142 67 | $91 | Shoes R Us, Inc. | QQR |
142 68 | $842 | We Need Shoes, Co. | QQR |
142 69 | $736 | Laces Are In, Inc. | QQR |
142 70 | $721 | Laces Out, LLC | QQR |
Partner 3 Table 1
Acct Full Name | Acct Code | Product | Value |
---|---|---|---|
Cherry Company | 1 | AFD | $29 |
Apple Company | 4 | AFD | $753 |
Plum Company | 5 | DFM | $64 |
Grape Company | 6 | AFD | $284 |
Partner 4 Table 1
ID# | Formal Customer Name | Prod.ID# | Customer State |
---|---|---|---|
863 | Jelly Company Inc | TYY | CT |
864 | Jam & Fresh Company | YTM | IA |
865 | Peanuts with Butter on Bread LLC | TYY | NY |
868 | Bread with no Butter Inc. | TYY | UT |
Partner 4 Table 2
Informal Name | ID# | Current Value | Shares |
---|---|---|---|
RT & P Inc | 861 | $123 | 5 |
J&F Co. | 864 | $146 | 5 |
Bw/oB,Inc. | 868 | $634 | 8 |
A&P Inc | 870 | $482 | 4 |
Ref Table 1
PRODUCT IDS | Product Inception |
---|---|
TYY | 7/13/09 |
DFM | 12/14/03 |
QQR | 1/26/08 |
YTM | 5/4/13 |
RML | 2/27/07 |
AFD | 2/26/17 |
QPL | 11/4/06 |
LTY | 1/11/11 |
Super confidential information; can't provide a direct example from the files.
Bump?! Not sure about the protocol on bumping a thread around here...