Qlik Community

Ask a Question

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor
Contributor

Using a Variable to determine Field Name

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

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

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

View solution in original post

5 Replies
MVP & Luminary
MVP & Luminary

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

View solution in original post

Contributor
Contributor

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.  

Partner
Partner

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.
MVP & Luminary
MVP & Luminary

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

Contributor
Contributor

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