topic Re: Dynamic Field name based on Dimension value in QlikView

Dynamic Field name based on Dimension value

Anonymous — Wed, 27 Dec 2017 15:19:14 GMT

Hello community,

I've been looking for a solution to this, but did not find anything on qlik community nor outside of it, does anyone know if it can be solved?:

I've a table with year and quarterly data that has the following columns:

sales_q_1, sales_q_2, sales_q_3, sales_q_4, costs_q_1 [, .... 4], etc.

Now i'm doing a table with dimension quarter (1,2,3,4),

Is it possible to have the expression something like 'Sales_q_' & quarter - so that the field name is evaluated four times, as per each quarter?

I tried the expressions as in this thread (this is the closest i found): dynamic field name‌

With something like Sum($(sales_q_'&quarter)) and various other attempts, but i cannot get it working, am i doing something wrong, or is it not achievable?

Re: Dynamic Field name based on Dimension value

YoussefBelloum — Wed, 27 Dec 2017 15:24:45 GMT

Hi,

if you already created the Field Quarter, you can us it on an If statement on the expression part of an object, like this:

If( Quarter=1, sum(Sales_q) )

Did you try it ?

Re: Dynamic Field name based on Dimension value

Anonymous — Wed, 27 Dec 2017 15:27:22 GMT

yes, like this:

if (quarter=1, sales_q_1, if (quarter=2, sales_q_2, if (quarter=3, sales_q_3, sales_q_4)))

But i was thinking something more elegant, a single expression like

[sales_q_$(quarter)]

Re: Dynamic Field name based on Dimension value

YoussefBelloum — Wed, 27 Dec 2017 15:33:00 GMT

yes i understand you,

another option to use only One if, is to concatenate the line of Sales_Q to remove the number from that field and replace it with a second field containing only the number of the quarter..

like that you will have:

if( Quarter = $(vQuarter), Sales_Q)

Re: Dynamic Field name based on Dimension value

Anonymous — Wed, 27 Dec 2017 15:37:45 GMT

Youssef, I'm sorry, you're missing the point, i don't need any If's,

I need to display all quarters sales,

The dimension is Quarter, with values: 1,2,3,4.

Can i have an expression, that builds the field name from this quarter value: e.g.: sales_q_[QUARTER] ?

Re: Dynamic Field name based on Dimension value

Anonymous — Wed, 27 Dec 2017 15:53:24 GMT

sum({1<Quarter={1,2,3,4}>} Sales)

sum({$<Quarter={$(=ONLY(Quarter )-1)}>} Sales)

Re: Dynamic Field name based on Dimension value

Anonymous — Wed, 27 Dec 2017 16:03:31 GMT

i would see this valid only when i load already all field names transformed into a single one,

e.g. load 1 as quarter, sales_q_1 as sales, load 2 as quarter, sales_q_2 as sales, etc.

But i have the field names as stated: sales_q_1, sales_q_2, sales_q_3, etc.

Re: Dynamic Field name based on Dimension value

mdmukramali — Wed, 27 Dec 2017 16:23:30 GMT

Hi Lucian,

Quick through why don't you try to use Cross Table Concept

Like Quarter field : Values Q1 ,Q2 ,Q3 and Q4

and Sales Field : Sales of each Quarter .

if you can attach sample data it will be helpful.

maybe i'm missing something ?

Thanks,

Mukram.