Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I am creating an Excel add-in, to directly import data from Qlik Sense to Excel. I am using the toolbox Qlik Sense .net SDK. I encountered a fatal error when I tried to retrieve the 'qExpansionState' of a pivot table and here is the error message:
Newtonsoft.Json.JsonSerializationException: 'Error converting value "BDgyMTEwMDAwMDAtLUluY29tZSAvIChMb3NzKSBmcm9tIE9wZXJhdGlvbnMA" to
type 'Qlik.Engine.Blob'. Path 'qExpansionState[0].qPos.qElemValues[0]', line 1, position 5788.'
Inner Exception
ArgumentException: Could not cast or convert from System.String to Qlik.Engine.Blob.
I can't programmatically manipulate the expansion state of a pivot table until this issue is solved. It will be very kind of you to give me some advice or an alternate solution. Thank you very much.
That's very interesting! Looks like you have found a deserializer bug in the SDK! Those "Blob" constructs are quite rare in the engine API and it seems the SDK doesn't deserialize them correctly.
But it's possible to work around this issue by using the AbstractStructure class. Here is an example on how to set the first expansion state entry of a pivot table hypercube:
var pivotTable = app.GetGenericObject(objId);
var props = pivotTable.GetProperties();
var cube = props.Get<HyperCubeDef>("qHyperCubeDef");
var expStates = cube.Get<AbstractStructure[]>("qExpansionState");
var obj = JObject.Parse("{\"qPos\":{\"qDimName\":\"Dim1\",\"qElemNo\":[1],\"qElemValues\":[\"BEIA\"]}}");
var newExpState = new AbstractStructure(obj);
expStates[0] = newExpState;
pivotTable.SetProperties(props);
In that case the new ExpansionState instance is created from a string representation of the underlying JSON structure. If you prefer to use object annotation, then you can do like this:
var obj = JObject.FromObject(new
{
qPos = new
{
qDimName = "Dim1",
qElemNo = new[] { 1 },
qElemValues = new[] { "BEIA" }
}
});
var newExpState = new AbstractStructure(obj);
Now I don't know how that "qElemValues" property is encoded. The engine API documentation doesn't document that part, so I'm afraid I can't help you there. But if you want to for instance reset a value to one that you know is correct, then this flow should work.
How are you accessing that property? A "Blob" is not really a string, but raw binary data I believe:
https://help.qlik.com/en-US/sense-developer/August2022/Subsystems/EngineJSONAPI/Content/models-blob....
Hi Øystein, thank you very much for your reply. What I want to do is getting the expansion state of a pivot table with Qlik Sense .net SDK.
Here is the result with JSON API, the qExpansionState is an array with 6 elements.
Now I am doing the same thing with the following c# code.
the result of line 1 is, expstate = null, which means the code didn't retrieve qExpansionState properly. Then I add some additional code to check the conversion.
Here is the result of line 2, which shows qExpansionState as json, and everything looks fine.
However when I try to convert this json to List<ExpansionData>, an exception is thrown(in line 3).
The error comes from the conversion of qElemValues from System.String to Qlik.Engine.Blob. And I think the null value of expState in line1 is also due to the same reason, however the exception is caught and just returns a null value.
What do you think about this exception? Looking forward to your relply. Many thanks!
That's very interesting! Looks like you have found a deserializer bug in the SDK! Those "Blob" constructs are quite rare in the engine API and it seems the SDK doesn't deserialize them correctly.
But it's possible to work around this issue by using the AbstractStructure class. Here is an example on how to set the first expansion state entry of a pivot table hypercube:
var pivotTable = app.GetGenericObject(objId);
var props = pivotTable.GetProperties();
var cube = props.Get<HyperCubeDef>("qHyperCubeDef");
var expStates = cube.Get<AbstractStructure[]>("qExpansionState");
var obj = JObject.Parse("{\"qPos\":{\"qDimName\":\"Dim1\",\"qElemNo\":[1],\"qElemValues\":[\"BEIA\"]}}");
var newExpState = new AbstractStructure(obj);
expStates[0] = newExpState;
pivotTable.SetProperties(props);
In that case the new ExpansionState instance is created from a string representation of the underlying JSON structure. If you prefer to use object annotation, then you can do like this:
var obj = JObject.FromObject(new
{
qPos = new
{
qDimName = "Dim1",
qElemNo = new[] { 1 },
qElemValues = new[] { "BEIA" }
}
});
var newExpState = new AbstractStructure(obj);
Now I don't know how that "qElemValues" property is encoded. The engine API documentation doesn't document that part, so I'm afraid I can't help you there. But if you want to for instance reset a value to one that you know is correct, then this flow should work.
Hi Øystein, thank you very much for your solution!
A quick follow up on this. A fix will be available in the next minor release of the .NET SDK which is currently targeted for November 2022.