topic Re: How to get index of a column from a table in expression? in QlikView

How to get index of a column from a table in expression?

Anonymous — Tue, 10 Jul 2018 20:25:38 GMT

I'm trying to use a table which has the z values and it's matching percentile based on the z values I've generated. I was able to use the fieldindex value to create a column which each rows respective index but I just can't get how to reference the column next to the index's value to put in.

I'm currently trying to use the column() function to pass back where the index is equal so I can get its value but I keep getting - or error. Here is my function: ([-3.89] is the index row which goes from -.389, -3.88, -3.87... 3.97, 3.98, 3.99, [5.0122111001305e-05] is the column with the values i'm trying to get, and column(7) is the column containing the indexes of where the value I need to get is.

Only({<[-3.89]={"=column(7)"}>} [5.0122111001305e-05])

If this doesn't work, how can I easily return an element from a loaded data table using its index? I've already tried fieldvalue as well which didn't work.

Re: How to get index of a column from a table in expression?

vishsaggi — Tue, 10 Jul 2018 23:28:18 GMT

Can you share some sample data to look into? And your expected output.

Re: How to get index of a column from a table in expression?

Anonymous — Wed, 11 Jul 2018 12:15:18 GMT

Data would be something like:

-3.89	5.01E-05
-3.88	5.22E-05
-3.87	5.44E-05
-3.86	5.67E-05
-3.85	5.91E-05
-3.84	6.15E-05
-3.83	6.41E-05
-3.82	6.67E-05
-3.81	6.95E-05
-3.8	7.23E-05
-3.79	7.53E-05
-3.78	7.84E-05
-3.77	8.16E-05
-3.76	8.5E-05
-3.75	8.84E-05
-3.74	9.2E-05
-3.73	9.57E-05
-3.72	9.96E-05
-3.71	0.000104
-3.7	0.000108
-3.69	0.000112
-3.68	0.000117
-3.67	0.000121
-3.66	0.000126

for the table:

and the other column of positions in the table:

555

...

etc.

would return index 225, 332, 444... from the 5.01E-05 column to fill the column with its correlating value i.e.

5.01E-05

5.22E-05

5.44E-05

5.91E-05

value to the right of the 555th row of the -3.89 column in the data table

Re: How to get index of a column from a table in expression?

vishsaggi — Wed, 11 Jul 2018 20:05:38 GMT

So based on your above data what is that you are trying to achieve. To your initial question you asked i believe you need ApplyMap to map the values to your table. Like

So for each fieldvalue of Fieldname1 you will get the corresponding value of FieldName2 in your FinalTab table.

Map:

Mapping LOAD * INLINE [

Fieldname1, FieldName2

-3.89, 5.01E-05

-3.88, 5.22E-05

-3.87, 5.44E-05

-3.86, 5.67E-05

-3.85, 5.91E-05

-3.84, 6.15E-05

-3.83, 6.41E-05

-3.82, 6.67E-05

-3.81, 6.95E-05

-3.8, 7.23E-05

-3.79, 7.53E-05

-3.78, 7.84E-05

-3.77, 8.16E-05

-3.76, 8.5E-05

-3.75, 8.84E-05

-3.74, 9.2E-05

-3.73, 9.57E-05

-3.72, 9.96E-05

-3.71, 0.000104

-3.7, 0.000108

-3.69, 0.000112

-3.68, 0.000117

-3.67, 0.000121

-3.66, 0.000126

];

FinalTab:

LOAD *,

ApplyMap('Map', FieldName1) AS MapFieldName

From YourTablename;