Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I would appreciate any transformation help to this issue:
Source data are stored like:
Fund | Price |
---|---|
Money Market funds | - |
MMF1 | 0.10 |
MMF2 | 0.12 |
Equity Funds | - |
EF1 | 102 |
EF2 | 96 |
EF3 | 87 |
... | ... |
Need transformation to:
Fund | Fund Category | Price |
---|---|---|
MMF1 | Money Market funds | 0.10 |
MMF2 | Money Market funds | 0.12 |
EF1 | Equity Funds | 102 |
EF2 | Equity Funds | 96 |
EF3 | Equity Funds | 87 |
Source contains more than 10 Fund Categories each includes hundreds of Funds.
Thank you in advance.
M
Is the null in Price the only indication of the fund categories?
LOAD *
Where Not(IsNull(Fund));
LOAD
If(IsNull(Price), Fund, Peek(Category)) As Category,
If(IsNull(Price), null(), Fund) As Fund,
Price
From ....
Yes it is,
are you sure, that your solution separates 'right' Fund values and creates Category dimension only for those rows stored below 'incorrect' Fund value until new 'incorrect' value is reached?
Hi,
Please find the attached qvw.
Regards,
Jemimah
Well it takes the Fund field for the category name when the Price is null, otherwise it takes the previous value of Category. So the category continues until the next null in Price. Does that answer your question?
Thank you for your answer, but it does not work as Load on Load.
But I appreciate your help very much.
You are right Jemimah, but I used null() instead of '-'.