topic Re: How do I set a Variable to a Field? in QlikView

How do I set a Variable to a Field?

Wed, 25 Nov 2020 16:16:04 GMT

Let iJobLab = [Inventory Job Code];
Sum (
{$<[Cost Job Code]=${iJobLab}>},

[JobStep Labour Value Estimated])

[Inventory Job Code] is a dimension in my chart. The [Inventory Job Code] is the same as the [Cost Job Code] only in different tables

What I am trying to do is set the [Cost Job Code] equal to the [Inventory Job Code] so I can pull data for different tables. I want the end result to look like:

InvHistory Transaction Date	Inventory Item Code	Inventory Job Code	Shipment COGS	Let iJobLab
01/04/2016	12345	209092	-21,472.52	10,000
01/05/2016	6789	209093	-20,000.00	5000

How do I make this happen within the expression?

Thank you

Re: How do I set a Variable to a Field?

amayuresh — Wed, 30 Mar 2016 05:33:08 GMT

Firstly variable only hold single value at a time, not set of values.

[Cost Job Code] and [Inventory Job Code] is present in separate table or in same table ?

if both field have same values then you can use directly [Cost Job Code] or [Inventory Job Code] as dimension in your chart / table.

Re: How do I set a Variable to a Field?

Gysbert_Wassenaar — Wed, 30 Mar 2016 05:44:27 GMT

1. associating tables is done in the script by giving the fields that should link the table the same name

2. you can't use set analysis in the script

3. the = inside 'Sum ( {$<[Cost Job Code]=${iJobLab}>} [JobStep Labour Value Estimated])' is a 'comparison' =, not an 'assignment' =.

4. I have no idea what you're trying to do. Please post a small qlikview document that explains things.

Re: How do I set a Variable to a Field?

Hatus — Wed, 30 Mar 2016 05:50:03 GMT

It is possible to sign one single value to the variable. For this matter I would use the peek() function in the script to take a specific value to work with.

Otherwise you can try to access the dimension value if you write a "if condition" for your sum:

sum(

if([Cost Job Code] = [Inventory Job Code], [JobStep Labour Value Estimated]

)

Re: How do I set a Variable to a Field?

settu_periasamy — Wed, 30 Mar 2016 05:53:40 GMT

Hi,

Let iJobLab = [Inventory Job Code]; => This will not assign your value to the Variable. It should be within single Quote.

it should be like

LET iJobLab = '[Inventory Job Code]';

And we can't compare the value inside set analysis. see here.. set_analysis_intra-record.qvw

So, your expression may be like this..

Sum (if([Cost Job Code]=$(iJobLab),[JobStep Labour Value Estimated]))

Re: How do I set a Variable to a Field?

Kushal_Chawda — Wed, 30 Mar 2016 05:59:15 GMT

You casn try the apply map function to create the flag for matching the code

InventoryCode:

mapping Load Key,

InventoryCode

FROM Table1;

here Key is link between the table in which Inventory Code and Job code is available

Table:

Load * ,

if(not isnull(applymap('Map',Key,null()),1,0) as CodeMatchFlag

FROM Table2;

Now you can write the expression

=sum({<CodeMatchFlag={1}>}[JobStep Labour Value Estimated]))