Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Janneke
Creator
Creator

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
jensmunnichs
Creator III
Creator III

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.

View solution in original post

3 Replies
jensmunnichs
Creator III
Creator III

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
Creator
Creator
Author

Hallo Jens,

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

Janneke.

jensmunnichs
Creator III
Creator III

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