Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

sri_c003
Contributor

FieldValue in a table, for non distinct table column values

Table:

ColA    ColB

A          A1

B          B1

C          A1

D          A1

E          C1

F          B1

When I do a FieldValue('ColB', 5) it is not returning anything. This is cos FieldValue takes distinct values of the column, and picking from that, pretty much defeating the column look up functionality.

Is there any way to achieve this, using the values in ColB as they are and not eliminating duplicates.

Wanted: FieldValue('ColB', 5) should return C1.

Note: This is to be used in Charts, not on scripting side.

Tags (2)
1 Solution

Accepted Solutions
felipedl
Valued Contributor III

Re: FieldValue in a table, for non distinct table column values

Sorry, misread the Note you sent.

You could use the only statement combined with set analysis to verify the value you want.

Example:

Data generating script:

x:

load * Inline

[

ColA   ,ColB

A          ,A1

B          ,B1

C          ,A1

D          ,A1

E          ,C1

F          ,B1

];

data:

Load

RowNo() as Index,

*

Resident x;

drop table x;

// End of script part

Chart expressions:

only({<ColA={'B'}>}ColB)

only({<Index={1}>}ColB)

Which gives:

Sample.png

4 Replies
felipedl
Valued Contributor III

Re: FieldValue in a table, for non distinct table column values

Hi Srinivas,

Use peek instead of fieldvalue, like below:

x:

load * Inline

[

ColA   ,ColB

A          ,A1

B          ,B1

C          ,A1

D          ,A1

E          ,C1

F          ,B1

];

let x = peek('ColB',0,'x');

This will return 'A1' (the index starts as 0) and say you want the 5th row value, would be:

let x = peek('ColB',4,'x');, which returns 'C1'.


Felipe.

sri_c003
Contributor

Re: FieldValue in a table, for non distinct table column values

Thanks Felip, but Peek is a script only function. I need the look up in chart.

felipedl
Valued Contributor III

Re: FieldValue in a table, for non distinct table column values

Sorry, misread the Note you sent.

You could use the only statement combined with set analysis to verify the value you want.

Example:

Data generating script:

x:

load * Inline

[

ColA   ,ColB

A          ,A1

B          ,B1

C          ,A1

D          ,A1

E          ,C1

F          ,B1

];

data:

Load

RowNo() as Index,

*

Resident x;

drop table x;

// End of script part

Chart expressions:

only({<ColA={'B'}>}ColB)

only({<Index={1}>}ColB)

Which gives:

Sample.png

sri_c003
Contributor

Re: FieldValue in a table, for non distinct table column values

Thanks Felip... shall try this approach, though a chart only change would have been ideal.