How To Get Row No Of A Specific Value -- FieldIndex Is Not Working In My Scenario

[#1]

Dear All,

May be some one can help me in this scenario, I just want to get row no of a specific value. For Example

myInLineTable:

CustomerNo, DocumentNo, Amount

cust-1, doc-1, 100

cust-1, doc-2, 100

cust-2, doc-3, 50

cust-1, doc-4, 90

I want to get the row no where CustomerNo in red on script, my required result is 3. I tried fieldindex on script but it is giving me 2

so please help me.

Regards

IndianQvLover

[hic]

Create a field in the script that displays this information:

Load *, recno() as RecNo inline ...

HIC

[#3]

Thanks for your reply but I am doing all this activity on script, I add recno() in table now please suggest how I will find

cust-2 in CustomerNo field and get its row or record no.

RecNo, CustomerNo, DocumentNo, Amount

1, cust-1, doc-1, 100

2, cust-1, doc-2, 100

3, cust-2, doc-3, 50

4, cust-1, doc-4, 90

Regards

IndianQvLover

[hic]

The simplest way is to use standard QlikView logic. Use a list box with CustomerNo and click on (or search for) 'cust-2'. Then you will immediately get the answer in the 'RecNo' column.

If you want to hard-code it (which I don't think is good practice) then you could use the following expression:

=only({1<CustomerNo={'cust-2'}>} RecNo)

HIC

[#5]

Sir,

No I don't want to hard-code it, it is not only for 'cust-2' only there is a lot of records, basically after find out that particular record I want to start a for .. next loop from that specific row no form some kind of data transformation on script.

Hope now I explain it properly

Regards

IndianQvLover

[hic]

If you want to find the first record that has 'cust-2' and then do some transformation on the following records - all in the script - then you could do something similar to the following:

MinRecNo:

Load Min(recno()) as MinRecNo from Data where CustomerNo='cust-2';

Let vRecNo = peek('MinRecNo', -1, 'MinRecNo');

Data:

Load <transformation> from Data where RecNo() > $(vRecNo) ;

... but I am not sure I understand what you are tryong to do.

HIC

[Dalton_Ruer]

The logical problem is that FieldIndex is finding the POSITION of the value within the ColumnValues that are unique. The table doesn't really exist. There are just pointers in the table to the value in the columnar table of unique values. 

So in the problem case the first 2 rows contain the same value Cust1, while Cust2 is on the 3'rd loaded row, the reality is in the columnar table it is indeed position 2. 

I'm sure in the original questioners question it wasn't really about the value Cust-2. Guessing, like in my case, there are other values in the table and what they were doing was trying to find the ROW for a given set of values, likely like in my case, from an entirely different table. 

Like the original author I was lucky in that my first 2 values were Row 1 and FieldIndex 1. And Row 2 and FieldIndex 2. But in my case, the second value was then repeated on 20 rows and the third value I tried to Peek for yielded a FieldIndex value of 3 but I need to know it's row 23. 

Question is years old but since it's fresh on my mind, after struggling for hours to understand why first test cases worked but next one failed, and this question comes up first when searching I wanted to help people understand the issue.