topic Re: Selecting a single value in a table in QlikView

Selecting a single value in a table

Anonymous — Mon, 15 Apr 2013 15:06:27 GMT

Hello QlikView Community,

I have a pivot table where I want to analyze the cost of different items, and their variations in time.

The data is made up by item code, cost code and unit cost, respetively (item_cd, cost_cd, unit_cost).

Each there is only 1 unit_cost for each item_cd, cost_cd combination.

I'm having troubles creating an expression in the Properties of the pivot table where the dimension is the item_cd the columns containing the data are 2 columns representing 2 different cost_cd (defined by some input variable) and the 3rd column would be the % of variation between these 2 columns, taking the 1st column as base.

The input variables are vCost_cd1 and vCost_cd2, defining the 2 different cost_cd of each item.

My expressions are the following:

=Only ( {$<cost_cd = {$(#vCost_cd1)}>} unit_cost)
=Only ( {$<cost_cd = {$(#vCost_cd2)}>} unit_cost)
=(column(2)-column(1))/(column(1))

Alternatively I also tried a double Only function:

=Only ( {$< cost_cd = {$ ( =Only(vCost_cd1) ) } > } unit_cost)
=Only ( {$< cost_cd = {$ ( =Only(vCost_cd2) ) } > } unit_cost)
=(column(2)-column(1))/(column(1))

Both of these attempts return an empty value in the pivot table. These are some solutions I came up after researching some of these functions in the community forum.

If I put only "unit_cost" in the Expression and open a Selection Windows and select the cost_cd there, it returns the correct unit_cost for me.

Thank you for your help!

Re: Selecting a single value in a table

Mon, 15 Apr 2013 16:27:08 GMT

Hi,

Assuming:

=Only ( {$< cost_cd = {$ ( =Only(vCost_cd1) ) } > } unit_cost) as Column1
=Only ( {$< cost_cd = {$ ( =Only(vCost_cd2) ) } > } unit_cost) as Column2

Try

=(Only ( {$< cost_cd = {$ ( =Only(vCost_cd2) ) } > } unit_cost) )-(Only ( {$< cost_cd = {$ ( =Only(vCost_cd1) ) } > } unit_cost))/(Only ( {$< cost_cd = {$ ( =Only(vCost_cd1) ) } > } unit_cost))

Rebeca

Re: Selecting a single value in a table

Anonymous — Mon, 15 Apr 2013 16:40:13 GMT

Hi Rebeca,

The problem isn't on the 3rd column, the problem is on the first 2 columns, which do not show the data in them.

I don't know the correct expression to make them show the unit_cost, having the cost_cd as a condition.

But I appreciate the reply anyway!

Re: Selecting a single value in a table

kaushiknsolanki — Mon, 15 Apr 2013 18:36:41 GMT

Hi,

If there is only 1 unit_cost for each item_cd, cost_cd combination, you can use "Sum" instead of the Only.

Regards,

Kaushik Solanki