Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Janneke
Contributor

CrossTable in script with dynamic n parameter

Hi all,

I have a script containing CrossTable() functionality:

     CrossTable(Year,FIELD_X,41)

This works fine.

However, I would like to change the '41' (n) into a function, representing the column number of a field. Now, whenever I add an extra dimension in the table, I need to change the '41' to '42' and so on.

Can I use something like this?

     CrossTable(Year,FIELD_X,ColumnNo(FIELD_Y))


Thanks for your help.


Janneke.

1 Solution

Accepted Solutions
Highlighted
jensmunnichs
Contributor III

Re: CrossTable in script with dynamic n parameter

Hey,

PFA

I don't think you can use a function in the Crosstable prefix, but you can use variables. So you have to load in original data, create variable based on FieldNumber(FIELD_Y), then use that and drop the original data (if necessary). I did this in tab Main in the attached file (uncomment first and comment main2 for it to work).

I think it's worth mentioning, if the number of fields that need to be crosstabled is always the same, it might be easier to use NoOfFields instead. I did this in the main 2 tab instead. In that case you don't need a field to show where the qualifying fields end that'll just get dropped later, or (if it's a field that actually has useful data that you want to keep), it gives you the option to add more dimensions on the right side of this field instead of just on the left side.

Do this by making your variable NoOfFields('Table1') - x, where x is the number of fields that need to be crosstabled.

Hope this helps!

EDIT: To test if either option works, reload, see the table box, then comment ID2, and you'll see it still makes the crosstable correctly.

3 Replies
Highlighted
jensmunnichs
Contributor III

Re: CrossTable in script with dynamic n parameter

Hey,

PFA

I don't think you can use a function in the Crosstable prefix, but you can use variables. So you have to load in original data, create variable based on FieldNumber(FIELD_Y), then use that and drop the original data (if necessary). I did this in tab Main in the attached file (uncomment first and comment main2 for it to work).

I think it's worth mentioning, if the number of fields that need to be crosstabled is always the same, it might be easier to use NoOfFields instead. I did this in the main 2 tab instead. In that case you don't need a field to show where the qualifying fields end that'll just get dropped later, or (if it's a field that actually has useful data that you want to keep), it gives you the option to add more dimensions on the right side of this field instead of just on the left side.

Do this by making your variable NoOfFields('Table1') - x, where x is the number of fields that need to be crosstabled.

Hope this helps!

EDIT: To test if either option works, reload, see the table box, then comment ID2, and you'll see it still makes the crosstable correctly.

Janneke
Contributor

Re: CrossTable in script with dynamic n parameter

Hallo Jens,

This did the trick! Thank you very much for your assistance.

Janneke.

jensmunnichs
Contributor III

Re: CrossTable in script with dynamic n parameter

Hey,

Glad it worked, make sure to mark the answer as correct so other forum users know they don't need to open this question anymore

Qlik Community Tip: Marking Replies as Correct or Helpful