Hi,
I am a novice on using Qlik Sense in particular and BI tools in general.
I have a Plan of Accounts in the central system, which I access through ODBC. And I want to create a local report which uses a different Plan of Accounts schema. How can I make their connection?
Simplifying the situation:
Table A – Plan of accounts accessed by ODBC, and already hierarchized:
Account#; Description; ParentAccount#; Level1_Account_Description; Level2_Account_Description; Level3_Account_Description; Level4_Account_Description; Level5_Account_Description
Table B – Accounts values accessed also by ODBC:
Account#; Date; Value
Table C – Local Plan of Accounts in Excel:
New_Lvl1_PoA_Description; New_Level2_Account_Description; New_Level3_Account_Description; New_Level4_Account_Description; Corresponding_Official_Account#
Note1: The official Account# is in text format
Note2: The “Corresponding_Official_Account#” can be on any level of the official PoA (from level 2 to level 5)
Note3: Some of the local PoA accounts can be the aggregation of several official PoA accounts. So on table C it can appear several times (one line for each of the official accounts it is related to).
I loaded both tables, and Qlik Sense automatically related both tables. Good!
Then, I created a Pivot Table and added as line dimensions:
- 1. New_Lvl1_PoA_Description (from Table C);
- 2. New_Level2_Account_Description (from Table C);
- 3. New_Level3_Account_Description (from Table C);
- 4. New_Level4_Account_Description (from Table C);
- 5. Description (from table A)
And it drills down. But when I get into the level of table C, it becomes empty and there is no way to drill-up anymore.
Any idea on how can I join Table A and Table C, so that from Table C I can access Table B?
Thank you,
Rui
