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: 
mato32188
Specialist
Specialist

Field value as new dimension transformation

Hello,

I would appreciate any transformation help to this issue:

Source data are stored like:

FundPrice
Money Market funds-
MMF10.10
MMF20.12
Equity Funds-
EF1102
EF296
EF387
......

Need transformation to:

FundFund CategoryPrice
MMF1Money Market funds0.10
MMF2Money Market funds0.12
EF1Equity Funds102
EF2Equity Funds96
EF3Equity Funds87

Source contains more than 10 Fund Categories each includes hundreds of Funds.

Thank you in advance.

M

ECG line chart is the most important visualization in your life.
1 Solution

Accepted Solutions
Not applicable

Hi,

Please find the attached qvw.

Regards,

Jemimah

View solution in original post

6 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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 ....

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
mato32188
Specialist
Specialist
Author

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?

ECG line chart is the most important visualization in your life.
Not applicable

Hi,

Please find the attached qvw.

Regards,

Jemimah

jonathandienst
Partner - Champion III
Partner - Champion III

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?

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
mato32188
Specialist
Specialist
Author

Thank you for your answer, but it does not work as Load on Load.

But I appreciate your help very much.

ECG line chart is the most important visualization in your life.
mato32188
Specialist
Specialist
Author

You are right Jemimah, but I used null() instead of '-'.

ECG line chart is the most important visualization in your life.