topic Problem with Column of fields in QlikView

Problem with Column of fields

Tue, 09 Feb 2010 17:44:08 GMT

Hello everybody,

I have a problem which bothers me a lot. And I hope anyone of you can help me...

In my data model I have 3 different fields, called Col1, Col2 and Col3. Furthermore I have a field relevantCol which consists of the names of the three fields before. Now I would like to create an expression which returns the values of the columns mentioned in relevantCol.

I tried to use the expression

$(=relevantCol)

But this works only if one unique relevantCol has been chosen.

I think it will be clear if you have a look at the attached qvw. Just choose one item under relevantCol.

Is it possible to modify the expression in a way that it shows always the correct value?

Thanks a lot!

Problem with Column of fields

Tue, 09 Feb 2010 17:55:57 GMT

I think you want:

GetFieldSelections(relevantCol)

Problem with Column of fields

Tue, 09 Feb 2010 18:22:43 GMT

Thanks,

but I don't want the field names of the selected columns. I need the values of the listed columns under relevantCol:

NeededCol

Problem with Column of fields

Tue, 09 Feb 2010 18:30:03 GMT

Sorry, I misunderstood.

I posted a sample. Is that what you're looking for? I had to use an if...then, I don't know if there is an easier way to do it. I also suppose that your real project is more complicated than the example.

Problem with Column of fields

Tue, 09 Feb 2010 18:39:26 GMT

Unfortunately it is, indeed. The result is exactly what I am looking for. But I need it in a more abstract solution cause there will be more columns than 1 to 3. But it's not clear how much more, cause they can will be added dynamically.

I need something like lookup for charts. But unfortunately this function is only support in the script. But I don't want to implement this in the script (for other reasons...).

Problem with Column of fields

rwunderlich — Tue, 09 Feb 2010 20:27:02 GMT

If you can add a "Row" field to use as an index in your data table, you could use a FieldValue() function like this:

=FieldValue(relevantCol, Row)

Note the absence of quotes around relevantCol, causing the value of relevantCol to be used. If you have more than one row per dimension you will have to use aggregation expressions to get relevantCol and Row.

Example attached.

-Rob

Problem with Column of fields

Tue, 09 Feb 2010 20:42:42 GMT

Dear Rob,

My experience is that FieldValue function works only if disctinct values are present in all the rows of the lookup value column. Even in your example, if you repeat any value in the Col1,Col2,Col3 the lookup value will not come correctly.

Please let me know if this understanding is correct.

Nimish

Problem with Column of fields

rwunderlich — Tue, 09 Feb 2010 20:55:30 GMT

Ouch. Nimish, you are correct. FieldValue holds the distinct values, not the number of rows. My example won't work.

-Rob

Problem with Column of fields

Wed, 10 Feb 2010 11:05:20 GMT

Thanks,

I also thought already about FieldValue, which failed as you already mentioned because of the value which do not have to be distinct...

But maybe s.o. else has a solution?

Problem with Column of fields

rwunderlich — Thu, 11 Feb 2010 01:58:11 GMT

This one drove my crazy. Seems there ought to be a way. If you could build in the script a variation of NMillers sugestion. An expression like:

=pick(match(relevantCol, 'Col1', 'Col2', 'Col3'), Col1, Col2, Col3)

I think that would do the trick. If you didn't want to do anything in the script, you could build it dynamically. Assign the following expression to a variable (vPickExpr):

='pick(match(relevantCol, ' & chr(39) & concat($Field,chr(39) & ',' & chr(39)) & chr(39) & '), ' & concat($Field, ',') & ')'

and then use

=$(vPickExpr)

as your chart expression. It includes all the Fields in the model, but gives the correct result given your sample data. Don't know how it would scale up.

Example attached including some columns not related to the chart.

-Rob

Problem with Column of fields

Thu, 11 Feb 2010 14:47:35 GMT

Great solution!

Thanks a lot.

Unfortunately, I can't use this expression cause of performance reasons... (I need this formula about 10 times for ca. 1 Mio rows.)

😞

But that was exactly what I needed.

Best regards,

Dae-Wie

Problem with Column of fields

rwunderlich — Thu, 11 Feb 2010 16:57:09 GMT

Dae-Wie,

I'm sorry the performannce didn't work out.

I'm curious about the scenario you are trying to use this in. Can you describe your real world applicaion of this?

Thanks,

Rob

Problem with Column of fields

johnw — Thu, 11 Feb 2010 22:54:06 GMT

It seems like we should be able to do evaluate(relevantCol). But QlikView explicitly disallows this function in charts. Why? No idea!

I'm guessing that the pick(match()) approach is slow because it's doing so very much string manipulation when setting up the variable. If it's doing it for every row of the chart, which seems likely, then that could certainly bog you down.

But maybe we're reading too much into where you said "...there will be more columns than 1 to 3. But it's not clear how much more, cause they can will be added dynamically."

What do you MEAN by added dynamically? You can't actually create fields anywhere but in the script. The only fields that can be referenced are fields loaded in the script. So I would think that building the variable in the script would be perfectly safe. But I'm not sure I'd even go that far. I think I'd just write the expression in the chart, and remember that if I add a field to the script that I want to use in the chart, I have to add it to the chart.

So I'm recommending that, for performance reasons, you simply use Rob's original expression (formatted below for easier verification that your literals match your field names):

pick(match(relevantCol
,'Col1','Col2','Col3')
, Col1 , Col2 , Col3 )