topic Re: how to get values for $Field in QlikView

how to get values for $Field

kristof_j — Mon, 04 Aug 2014 15:34:40 GMT

Is there a way to get values when you have $Field as dimension?

If I make an alternate state I want to see the largest differences for almost all the fields.

I have the expressions I need but they only work if you select one field. But this is inefficient if you have many fields.

Re: how to get values for $Field

kristof_j — Wed, 06 Aug 2014 07:22:16 GMT

no suggestions?

Re: how to get values for $Field

Anonymous — Wed, 06 Aug 2014 07:36:33 GMT

Linking $Field to individual column is really nightmare. Can you load data with cross table join and create field with all columns. You can then link data with individual columns.

CrossTable (Field, Data, 1)

LOAD * INLINE [

TransId, Dim1, Dim2, Dim3

1, A, a, 1

2, B, b, 2

3, C, c, 3

];

Re: Re: how to get values for $Field

tresB — Wed, 06 Aug 2014 07:39:51 GMT

Like this attached sample?

Note the enabling condition in the dimension tab, like: =Index(Concat(DISTINCT $Field),'Dim1')

Re: Re: how to get values for $Field

kristof_j — Wed, 06 Aug 2014 08:00:28 GMT

I added your table to the model but I don't understand what you mean.

In 'real' model we have over 100 field so it should also be automated

I added a new QVW with your suggested table.

Re: Re: Re: how to get values for $Field

Anonymous — Wed, 06 Aug 2014 08:08:10 GMT

See attachment

Re: Re: how to get values for $Field

kristof_j — Wed, 06 Aug 2014 08:11:01 GMT

The dimensions should be on the same level instead of different levels.

What we want is a table that indicates in which dimensions or values of dimensions the largest differences are between 2 groups.

The groups could be defined in an alternate state.

Re: Re: how to get values for $Field

kristof_j — Wed, 06 Aug 2014 08:19:20 GMT

Good suggestion but we still need to select on the dimensions. In our model we have +4million customers and 144 fields and some field can contain over 100 different values.

Re: Re: Re: how to get values for $Field

marcus_sommer — Wed, 06 Aug 2014 13:40:05 GMT

I'm not sure if it what you want - but I think it will be useful. Have a look on the sheet "Datastructure" within the attachment. Be careful with formulas like "Concat Fieldvalues" on larger apps - they need a lot of performance. For this you could use calculation-conditions on getselectedcount($Table or $Field) or similar.

- Marcus

Re: how to get values for $Field

kristof_j — Thu, 07 Aug 2014 07:27:54 GMT

We want to do some calculations for the fieldvalues.

You can compare it with a piot table but with all the dimensions on the first level.

If you add a dimension in a pivot table it will be on a lower level.

something like this with state1-2 and difference as expressions:

Field     Value     state1     state2     difference

A               1          10          10               0

                  2          15          5               10

B               1          0              5               -15

...

and not like an ordinary pivot table:

Dim A     Dim B      state1     state2     difference

     1               1          0               5               -5

     1               -          10               5               5

     2               1          0                   ...

Re: how to get values for $Field

marcus_sommer — Thu, 07 Aug 2014 08:45:12 GMT

Have a look on this posting Re: Chart with count of $Field values, especially the QVFieldObserver.qvw from Ralf Becher and the min/maxstring(Concat($Field)) expressions - I think you could adapt it.

But how above mentioned such solutions need a lot of performance. Perhaps it is an alternatively to (pre) calculate these within the script.

- Marcus

Re: how to get values for $Field

marcus_sommer — Fri, 08 Aug 2014 06:31:31 GMT

In addition to the post above you could integrate the different states into these expressions but I think the whole approach is already (too) complex and if you it get work the performance will be very bad. So I think you should look for another way to answers your final aim.

- Marcus