Forum Discussion
Generate table dynamically using slicer value
Hi Ira_27 ,
Let me start by apolozing for the questions but just want to make sure that I understand the requirement.
When you refer that you need to have the decomposion for each of the parent child what is exactly the information you need.
For example you have:
|
Parent |
Child |
|
0 |
|
| 0 | 1 |
| 1 | 2 |
| 2 | 3 |
| 2 | 4 |
You want to have a line for each one of this so you would get the following lines:
| 0 |
| 0-1 |
| 2-1-0 |
| 3-2-1-0 |
| 4-2-1-0 |
Is this understading correct?
Are you able to give a small sample of data like 2 or 3 parent child relations and what is the final outcome? A small mockup.
fyi, i am able to generate the data using GENERATE function. The problem is that when i deploy the model the calcualted table blows up because each date results in 80Million records.
I am looking for a way to either calculate this table on the fly for selected date and childid so that it limits the number of records or any other approach that will limit this decomposed data storage and run it adhoc
Here is the sample data and expected output.
| HoldingDate | AccountID | FundID | SecID | SecName | Qty | MV |
| 2025-01-01 | 123 | Sec A | Stock A | 10 | 100 | |
| 2025-01-01 | 123 | 996 | MF A | Mutual Fund A | 100 | 1000 |
| 2025-01-01 | 996 | 997 | MF B | Mutual Fund B | 50 | 5000 |
| 2025-01-01 | 996 | 998 | MF C | Mutual Fund C | 70 | 7000 |
| 2025-01-01 | 997 | Sec B | Stock B | 10 | 100 | |
| 2025-01-01 | 997 | Sec A | Stock A | 70 | 700 | |
| 2025-01-01 | 997 | Sec C | Stock C | 80 | 800 | |
| 2025-01-01 | 998 | Sec D | Stock D | 90 | 900 | |
| 2025-01-01 | 998 | Sec E | Stock E | 100 | 1000 | |
| 2025-01-01 | 998 | Sec F | Stock F | 110 | 1100 |
Expected Output
| HoldingDate | AccountID | FundID | SecID | SecName | Qty | MV |
| 2025-01-01 | 123 | Sec A | Stock A | 10 | 100 | |
| 2025-01-01 | 123 | Sec B | Stock B | 100 | 1000 | |
| 2025-01-01 | 123 | Sec A | Stock A | 50 | 5000 * (% holding) | |
| 2025-01-01 | 123 | Sec C | Stock C | 70 | 7000 * (% holding) | |
| 2025-01-01 | 123 | Sec D | Stock D | 10 | 100 * (% holding) | |
| 2025-01-01 | 123 | Sec E | Stock E | 70 | 700 * (% holding) | |
| 2025-01-01 | 123 | Sec F | Stock F | 80 | 800 * (% holding) |
Hi Ira_27 ,
Apologies for the question once again but trying to understand one thing, on your data you have the Mutual funds those are not appearing in your output why is that?
Also not getting what is you Holding % Doing the reverse calculation I get for example for Sec AStoick A 50 = 5000 * 1%For the first line is this the sum of the total Qty 690 / 10 = 1.45%