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: 
shahafei2
Creator
Creator

Pivot table and Variables error locator?

Hi all,

I have a Pivot table that contains lot of vars as expressions and dimensions

there is a way to locate which var/s contain an error that cause to an empty chart to be shown?

 

17 Replies
shahafei2
Creator
Creator
Author

DimensionID = rowno() as DimensionID = as HeaderID (if you look on the photo that i shared)
marcus_sommer

What happens by:

Pick(rowno(), 1,2,3,4,....) respectively Pick(rowno(TOTAL), 1,2,3,4,....)

- Marcus

shahafei2
Creator
Creator
Author

The rowno() is based on specific field in one table (i build an excel file that hold the following fields
Header , SubHeader, Measure , Calc
When
Header = Primary Dimension
SubHeader = Sub Dimension
Measure = Sub Expression - based on dimension tab
Calc = The unique Vars that i'm using in the $(Metric) var as Expression under Expression tab
marcus_sommer

I assume that your DimensionID isn't properly defined - it needs to be unique in regard to your 2 vertically and 2 horizontally dimensions. This means you might need a different table-structure for your approach.

- Marcus

shahafei2
Creator
Creator
Author

I checked the loaded table and the "DimensionID" field is unique and equals to unique "Calc" field value so for my understanding it should match between the Pivot table's RowNo() and the Calc of the Var as i used in the vMetrics var
marcus_sommer

Then try my above suggestion again and apply:

only(DimensionID)
count(DimensionID)
concat(DimensionID, ',')

within your table. Which one returned which results?

- Marcus

shahafei2
Creator
Creator
Author

The results:

only(DimensionID) - returns the rowno()

count(DimensionID) - returns  1 as value in each cell

concat(DimensionID, ',') - returns the rowno() - the same as received  by only(DimensionID)

 

 

 

marcus_sommer

Ok. This means that:

pick(only(DimensionID), 1,2,3,4,5)

should return a table with results and not just a blank table and further that your issue with the origin expression is caused by any invalid expression-variable and/or a mistake by placing the variables within the pick() which all together leads to a syntax-error for the whole expression. To find it I suggest to use my above mentioned commenting-approach within the expression.

Beside this you are trying to apply a quite large amount of different expressions. If each one is quite big you might hit any limitation in regard to the max. amount of chars within an expression. I never experienced such case but I could imagine that such things might happens. Further by larger datasets your approach might need some time to be calculated and/or hit RAM limitations for the object - but this should be clearly noticable.

Another thought goes to any comments within the variables which may not bother by using them as a single expression but by including them within other calculations they could cause problems, see: Well-commented-variables-Be-careful.

- Marcus