topic Re: How To Get Row No Of A Specific Value -- FieldIndex Is Not Working In My Scenario in QlikView

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

Sun, 14 Oct 2012 18:20:35 GMT

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

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

hic — Sun, 14 Oct 2012 18:45:17 GMT

Create a field in the script that displays this information:

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

HIC

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

Mon, 15 Oct 2012 04:28:14 GMT

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

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

hic — Mon, 15 Oct 2012 07:26:59 GMT

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

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

Mon, 15 Oct 2012 08:04:58 GMT

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

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

hic — Mon, 15 Oct 2012 08:27:12 GMT

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

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

Dalton_Ruer — Wed, 30 Dec 2020 23:57:16 GMT

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.