topic Using a Variable to determine Field Name in App Development

Using a Variable to determine Field Name

Luke_O — Tue, 12 Mar 2019 14:29:30 GMT

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

Re: Using a Variable to determine Field Name

marcus_sommer — Tue, 12 Mar 2019 15:54:23 GMT

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

Re: Using a Variable to determine Field Name

Luke_O — Tue, 12 Mar 2019 16:34:11 GMT

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.

Re: Using a Variable to determine Field Name

dplr-rn — Tue, 12 Mar 2019 19:20:29 GMT

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.

Re: Using a Variable to determine Field Name

marcus_sommer — Wed, 13 Mar 2019 08:28:54 GMT

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

Re: Using a Variable to determine Field Name

Luke_O — Wed, 13 Mar 2019 09:04:41 GMT

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