topic Re: Pick Field name Dynamically in expression in QlikView

Pick Field name Dynamically in expression

nisha_rai — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

Please help me to resolve the issue. Below is my scenario:

For example:

Item	Jan Price	Feb Price	March Price	April Price	May Price	June Price	July Price
A	10	5	23	15	50	12	11
B	20	70	10	5	11	23	1
C	5	20	10	10	25	30	15

I want to show the Sum of price based on the month selection as well as show the table box in which the structure is same which is shown above.

I am transposing the table but in table box i am not getting the same structure,Please help me to resolve the issue.

Regards,

Nisha

Re: Pick Field name Dynamically in expression

kaushiknsolanki — Fri, 21 Apr 2017 09:48:26 GMT

Dear Nisha,

You need to tell us more about your issue, this information is not giving us any idea about your problem.

Request you to upload some sample data and show us what is happening and what you require as output.

Regards,

Kaushik Solanki

Re: Pick Field name Dynamically in expression

nisha_rai — Fri, 21 Apr 2017 09:53:18 GMT

Hi Kaushik,

The table structure is same what i have posted,

I need to show the sum of price based on the month, First of all i have to find the month from the above table then calculte the sum of price what else the month is selected, if i transpose the table then that issue resolve but when we are creating the Table box that time, it's not possible to add the filed name such as Jan Price,Feb Price, and so on

So that I can't transpose the table, but in expression i have to pick the dynamic field based on the Month.

For Example:Sum("SelctedMonth" Price)

Re: Pick Field name Dynamically in expression

kaushiknsolanki — Fri, 21 Apr 2017 10:03:20 GMT

Hi,

If your data source is like the one which you posted, you can transpose it while loading it in Qlik using the Crosstable Function.

Regards,

Kaushik Solanki

Re: Pick Field name Dynamically in expression

nisha_rai — Fri, 21 Apr 2017 10:16:48 GMT

Hi ,

I don't want to transpose the data , because if we transpose that time in table box we can't show like that table

Item Jan Price FebPrice MarchPrice AprilPrice

Re: Pick Field name Dynamically in expression

kaushiknsolanki — Fri, 21 Apr 2017 10:26:55 GMT

Hi,

You can using the pivot table.

Regards,

Kaushik Solanki

Re: Pick Field name Dynamically in expression

nisha_rai — Fri, 21 Apr 2017 10:30:10 GMT

Hi,

But in Pivot we have the scroll bar only for the expression not for the whole table.

Regards,

Nisha

Re: Pick Field name Dynamically in expression

swuehl — Fri, 21 Apr 2017 10:31:51 GMT

I would also suggest to use CROSSTABLE in your script to transform your table.

If you really don't want to do this, and you have another field with month values that match the first part of your above field names, you can try this when a single month is selected:

=Sum( [$(=MonthField&' Price')])

The dollar sign expansion needs to expand to a field name.

Re: Pick Field name Dynamically in expression

nisha_rai — Fri, 21 Apr 2017 10:35:53 GMT

Hi Stefan,

I don't have any Month field, if we need to add the month then will you please help me how to add the month filed in that table.

Regards,

Nisha

Re: Pick Field name Dynamically in expression

swuehl — Fri, 21 Apr 2017 10:53:57 GMT

You were saying

"I want to show the Sum of price based on the month selection as well as show the table box in which the structure is same which is shown above."

So how do you select month?

If you need a new field, you can add a data island in your scenario (not using CROSSTABLE to transform your data):

// Monthfield for above suggested expression. Values need to match your table field names, first part.

LOAD * INLINE [

MonthField

Jan

Feb

March

April

May

June

July

];