Using a Variable to determine Field Name

[Luke_O]

Hi,

I am trying find a simple method of returning the field/column name where a variable is found within a record eg:

ID | Col2 | Col3 | Col 4 | Col 5

XX1 | abc | def | ghi | jkl  

XX2 | def | ghi | jkl | abc 

I'm looking for a script function that would return:

'Col4' or the value '4' when I use the value 'ghi' for ID =XX1

and

'Col3' or the value '3' when I use the value 'ghi' for ID =XX2

I have had some success concatenating Col1+Col2+Col3+Col4 and searching for the sub-string start position but as I'm intending to use this as an audit tool with several hundred rules this method is too labor intensive.

I'm sure it's something easy but couldn't find anything obvious when searching the net.

Thanks

Luke

[marcus_sommer · Accepted solution]

I think I would rather transform the crosstable to a normal table-structure and applying the checks on this result. To what is meant see: The-Crosstable-Load.

- Marcus

[Luke_O]

That could be a good solution but would likely result in me producing some very large tables, as I'm currently looking at approximately 2,000,000 records each with 48 fields that's why I have been using the concatenation method.

I'm sure it would work and may well be a better solution. I was hoping there was a function out there like FieldIndex() but instead of returning row number it produced column number/name.  

[dplr-rn]

designing in the way marcus mentioned is the most flexible approach.
Can i ask why you are worried about 2 million records? generally (if the datamodel is design is decent) 2 mill records is not a problem at all for Qlik.

[marcus_sommer]

It's difficult to say what the best approach for your special audit-checking might be but I assume that a table with just 3 columns with normal fieldvalues by 96 M records performed better as a table with 2 M records but with a quite huge string-concatenation - but it will depend on your requirements.

If your checkings are really independent from the records you could use a check against the symbol-tables which contain the distinct values of the fields.

If you provide some more details what should be checked and why - maybe there are other approaches thinkable.

- Marcus

[Luke_O]

Thanks Marcus,
Loaded the table as suggested this morning seemed to perform similar to concatenation method but, as you say certainly more flexible for analysis and require less scripting per audit rule.

I'm auditing health care data and patient records which is why I hadn't use any really data in my example.

All the best

Luke