Hello, I have an issue. I have a badly structured mongodb - data that looks like this:
example
This is two jsons from the mongodb database:
{
"_id" : ObjectId("12345"),
"campaign" : NumberInt(54312),
"profile" : "43214125432",
"transaction" : "35",
"platforms" : {
"e7bcd087e0e840c997b487e8118aad71" : {
"conversion" : 1.0,
"conversionAmount" : 287.08923360425405,
"currency" : "EUR"
}
}
{
"_id" : ObjectId("12332445"),
"campaign" : NumberInt(54323412),
"profile" : "123412341",
"transaction" : "35",
"platforms" : {
"532452351345" : {
"conversion" : 1.0,
"conversionAmount" : 110.08923360425405,
"currency" : "EUR"},
{
"6435634563456" : {
"conversion" : 2.0,
"conversionAmount" : 342.08923360425405,
"currency" : "EUR"},
}
When I connect the database and let qlik convert the json format to a tabular dataform the columns does not turn out as i want. The issue is that it creates one column for each platform key (e.g. "platforms_e7bcd087e0e840c997b487e8118aad71_conversion", "platforms_e7bcd087e0e840c997b487e8118aad71_conversionAmount" and "platforms_e7bcd087e0e840c997b487e8118aad71_currency"). This leads me to have over 150 columns, three for each platforms value and the data becomes very sparse.
Is the any way to flatten rename all platform keys to 0, 1 ,2 ... when connection to the database / loading the data?
rename example:
{
"_id" : ObjectId("12345"),
"campaign" : NumberInt(54312),
"profile" : "43214125432",
"transaction" : "35",
"platforms" : {
"0" : {
"conversion" : 1.0,
"conversionAmount" : 287.08923360425405,
"currency" : "EUR"
}
}
{
"_id" : ObjectId("12332445"),
"somthing" : NumberInt(54323412),
"profile" : "123412341",
"transaction" : "35",
"platforms" : {
"0" : {
"conversion" : 1.0,
"conversionAmount" : 110.08923360425405,
"currency" : "EUR"},
"1" : {
"conversion" : 1.0,
"conversionAmount" : 110.08923360425405,
"currency" : "EUR"}
}
Thanks!
