topic Re: Pivot table and Variables error locator? in QlikView

Pivot table and Variables error locator?

shahafei2 — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Pivot table and Variables error locator?

miguelbraga — Tue, 08 Jan 2019 16:05:52 GMT

Hey there,

Yes, it is possible. Do as follows:

1. Go and enable one by one expression and see which causes the error.

2. Only when you've checked that go to the expression(s) that causes the error and after that check if any variable works outside the pivot chart in a KPI object or in a textbox.

3. Search if the expression gives values and if its correctly formed (might have problems in set analysis or in the syntax). It helps if you remove the label to see witch values comes to the set analysis.

Try and simplify and divide the problem in little parts to check where comes the error. Search deep on your Script, Variables, Dimensions and Measures. I'm sure you could find which causes the error.

Best regards,

Re: Pivot table and Variables error locator?

marcus_sommer — Tue, 08 Jan 2019 16:07:22 GMT

Just disable them one after another (maybe also in groups) and you will find which ones don't work.

- Marcus

Re: Pivot table and Variables error locator?

shahafei2 — Wed, 09 Jan 2019 09:05:12 GMT

Thank you but the problem is that i'm using a Pick(DimensionID,v1(A),v2(A),v3(B),v4(B)) as the expression

because i have a Dimension and SubDimension and the expression should pick the right var for each cell in the table

Re: Pivot table and Variables error locator?

marcus_sommer — Wed, 09 Jan 2019 09:26:58 GMT

You could add another expression like: = 1 to enforce that the table is shown but if there are a syntax-errors within one of your variables your origin expression will be probably display NULL for all but maybe the expression-label (if you left it empty) may give you a hint by hovering with the mouse which ones are causing the issue.

If this doesn't work you could try to comment some parts of your expression like:

Pick(DimensionID,v1(A),v2(A)) // ,v3(B),v4(B))

Pick(DimensionID,v1(A), /* v2(A),v3(B), */ v4(B))

otherwise you will need to check each single variable.

Beside this I'm not sure if something like v1(A) returned always a valid expression - I use usually $(v1(A)) for it.

- Marcus

Re: Pivot table and Variables error locator?

miguelbraga — Wed, 09 Jan 2019 09:31:37 GMT

Maybe just maybe the problem is that you use the variables as is. Try using this instead:

=Pick(DimensionID,$(v1(A)),$(v2(A)),$(v3(B)),$(v4(B))).

Also, could you try and see in a text box if there is any value retrieved in each variable. Try in each variable like this:

1. First Text Box

=v1(A)

2. Second Text Box

=$(v1(A))

See if anything gives you meaningful values.

Additionally, what you want to acquire with the specific expression? What's your goal?

This could give us a hint on how the problem occurs.

Best regards,

Re: Pivot table and Variables error locator?

shahafei2 — Wed, 09 Jan 2019 13:47:52 GMT

When i check each one of them desperately in text box i see a result

but when i use them in one expression with pick function it's not showing the result in the pivot table

The HeaderID field is the RowNo of the rows in the Pivot table

Re: Pivot table and Variables error locator?

marcus_sommer — Wed, 09 Jan 2019 14:03:54 GMT

Are you sure that your pick-approach itself is working, just check it with:

Pick(DimensionID, 1,2,3,4,....)

- Marcus

Re: Pivot table and Variables error locator?

shahafei2 — Wed, 09 Jan 2019 14:40:30 GMT

I Know that it should work (I already saw it in a different model)

Re: Pivot table and Variables error locator?

marcus_sommer — Wed, 09 Jan 2019 15:03:23 GMT

It could only work if DimensionID is unique so that a only(DimensionID) will work - if not then use count(DimensionID) and/or concat(DimensionID, ',') to see what happens amd adjust it appropriate.

- Marcus

Re: Pivot table and Variables error locator?

shahafei2 — Wed, 09 Jan 2019 15:09:41 GMT

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

Re: Pivot table and Variables error locator?

marcus_sommer — Wed, 09 Jan 2019 15:14:21 GMT

What happens by:

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

- Marcus

Re: Pivot table and Variables error locator?

shahafei2 — Wed, 09 Jan 2019 15:20:04 GMT

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

Re: Pivot table and Variables error locator?

marcus_sommer — Wed, 09 Jan 2019 15:39:28 GMT

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

Re: Pivot table and Variables error locator?

shahafei2 — Thu, 10 Jan 2019 09:26:20 GMT

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

Re: Pivot table and Variables error locator?

marcus_sommer — Thu, 10 Jan 2019 09:52:40 GMT

Then try my above suggestion again and apply:

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

within your table. Which one returned which results?

- Marcus

Re: Pivot table and Variables error locator?

shahafei2 — Thu, 10 Jan 2019 10:23:33 GMT

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)

Re: Pivot table and Variables error locator?

marcus_sommer — Thu, 10 Jan 2019 10:56:05 GMT

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