Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Min/Max using Fieldvalue and fieldvaluecount with rowno

Im trying to understand the min and max using fieldvalue and fieldvaluecount

 

Names:

LOAD * inline [

"First name"|"Last name"|Initials|"Has cellphone"|Value

John|Anderson|JA|Yes|5

Sue|Brown|SB|Yes|7

Mark|Carr|MC |No|9

Peter|Devonshire|PD|No|11

Jane|Elliot|JE|Yes|13

Peter|Franc|PF|Yes|23 ] (delimiter is '|');

let x = FieldValue('Value',2);

trace $(x);

max:
load
min(Value) as min,
max(Value) as max;
Load
FieldValue('Valor',RecNo()) as Value,
RecNo() as i,
rowno() as j
AutoGenerate FieldValueCount('Value');

 

If I change the recno for rowno in this line: FieldValue('Valor',RecNo()) as Value,

the min and max doesnt work. But the tables loaded with columns i and j are the same with recno or rowno

 

does anyone have a clue?

 

thanks in advance

 

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Some clues, so let's call this an educated guess. The RecNo() function returns the record number of the source table. So that number definitely exists when that source record is processed. The RowNo() function returns the number of the record that's created in the target table. My guess is that the FieldValue function is executed just before the record in the target table actually exists and therefore the RowNo() can't return the value of that record yet. That would explain why RecordNo() works and RowNo() doesn't in this case.


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Some clues, so let's call this an educated guess. The RecNo() function returns the record number of the source table. So that number definitely exists when that source record is processed. The RowNo() function returns the number of the record that's created in the target table. My guess is that the FieldValue function is executed just before the record in the target table actually exists and therefore the RowNo() can't return the value of that record yet. That would explain why RecordNo() works and RowNo() doesn't in this case.


talk is cheap, supply exceeds demand

View solution in original post

Highlighted
Partner
Partner

it makes sense

but if you comment the min and max table, these tables are equal if you run with recno or rowno:

max:
Load
FieldValue('Valor',RecNo()) as Value,
RecNo() as i,
rowno() as j
AutoGenerate FieldValueCount('Value');

thank you for your time

 

Highlighted
MVP & Luminary
MVP & Luminary

Yes, because then the records end up in the target table instead of being pushed up into the preceding load which processes the records first before they finally end up in the target table.


talk is cheap, supply exceeds demand
Highlighted
Specialist II
Specialist II

@Gysbert_Wassenaar ,

I tried the same code without the preceeding load like below,here I see that Val col is returning '?' in the max table , can you please help me understand what is happening in this case

LOAD * inline [

"First name"|"Last name"|Initials|"Has cellphone"|Value

John|Anderson|JA|Yes|5

Sue|Brown|SB|Yes|7

Mark|Carr|MC |No|9

Peter|Devonshire|PD|No|11

Jane|Elliot|JE|Yes|13

Peter|Franc|PF|Yes|23
Peter|Franc|PF|Yes|33 ] (delimiter is '|');
let x=FieldValueCount('Value');
let y= FieldValue('Value',3);
trace $(x);
Trace $(y);

max:

Load
FieldValue('Value',RecNo()) as Val,
RecNo() as i,

rowno() as j
AutoGenerate FieldValueCount('Value');