topic Re: Field value as new dimension transformation in QlikView

Field value as new dimension transformation

mato32188 — Fri, 12 Jun 2015 09:52:02 GMT

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.

Re: Field value as new dimension transformation

jonathandienst — Fri, 12 Jun 2015 09:57:33 GMT

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

Re: Field value as new dimension transformation

mato32188 — Fri, 12 Jun 2015 10:35:10 GMT

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?

Re: Field value as new dimension transformation

Fri, 12 Jun 2015 10:36:17 GMT

Hi,

Please find the attached qvw.

Regards,

Jemimah

Re: Field value as new dimension transformation

jonathandienst — Fri, 12 Jun 2015 10:43:22 GMT

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?

Re: Field value as new dimension transformation

mato32188 — Fri, 12 Jun 2015 12:05:18 GMT

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

But I appreciate your help very much.

Re: Field value as new dimension transformation

mato32188 — Fri, 12 Jun 2015 12:06:24 GMT

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