I have my JSON data as follows:
My aim is to create a table consisting of Date, Country and URL. I end up having empty cells in the Date and Country column as shown below:
How do I resolve this?
Date
Votes
3 comments
-
Hi Spurthi!
Thanks for the post!
Looking at your JSON data, it looks like you have more URLs than Dates or Countries. With that in mind, you can work around this by either isolating out the 1st URL for each entry using [1] at the end of your JSON formula, or by repeating the relevant date and country value for each URL value. To repeat the value, you may be able to use a LOOKUP function to map the value in the URL column to the date value (NOTE: this assumes that each URL is unique). This formula would look like this:
LOOKUP(&URL Column, @url, @url/../../../name)
This would return the date value for each URL.
Hope this helps! Given that I don't have access to the JSON data directly, it's challenging to know the exact best approach. As such, If you require further support for this, please submit a ticket with us and we'll be happy to assist further! -
Hi Parker,
Thanks for your quick response. I have data from 3 days for two countries for multiple URLs.
Please find the complete JSON below. Could you please help?
{
"report": {
"type": "trended",
"elements": [
{
"id": "evar900",
"name": "Country Split (v809)"
},
{
"id": "page",
"name": "Page"
}
],
"reportSuite": {
"id": "mynet",
"name": "mynet.de\/at (Live)"
},
"period": "Mon. 24 May 2021 - Wed. 26 May 2021",
"metrics": [
{
"id": "uniquevisitors",
"name": "Unique Visitors",
"type": "number",
"decimals": 0,
"latency": 1731,
"current": false
}
],
"data": [
{
"name": "Mon. 24 May 2021",
"year": 2021,
"month": 5,
"day": 24,
"breakdown": [
{
"name": "de",
"url": "",
"counts": [
"121427"
],
"breakdown": [
{
"name": "repeat-shopnow-191329",
"url": "",
"counts": [
"410"
]
},
{
"name": "shopnow\/kuendigungswiderruf\/bestellen",
"url": "https:\/\/www.mynet.de\/shopnow\/kuendigungswiderruf\/bestellen",
"counts": [
"199"
]
},
{
"name": "shopnow\/repeat-offer",
"url": "https:\/\/www.mynet.de\/shopnow\/repeat-offer",
"counts": [
"64"
]
},
{
"name": "repeat-shopnow-190025",
"url": "",
"counts": [
"1"
]
}
],
"breakdownTotal": [
"121427"
]
},
{
"name": "at",
"url": "",
"counts": [
"10593"
],
"breakdown": [
{
"name": "shopnow\/repeat-offer",
"url": "https:\/\/www.mynet.de\/shopnow\/repeat-offer",
"counts": [
"4"
]
},
{
"name": "shopnow\/repeat-technischer-fehler-block",
"url": "https:\/\/www.mynet.at\/shopnow\/repeat-technischer-fehler-block",
"counts": [
"3"
]
},
{
"name": "shopnow\/kuendigungswiderruf\/bestellen",
"url": "https:\/\/www.mynet.de\/shopnow\/kuendigungswiderruf\/bestellen",
"counts": [
"2"
]
},
{
"name": "shopnow\/repeat-technischer-fehler-link-ungueltig",
"url": "https:\/\/www.mynet.at\/shopnow\/repeat-technischer-fehler-link-ungueltig",
"counts": [
"1"
]
}
],
"breakdownTotal": [
"10593"
]
}
],
"breakdownTotal": [
"133427"
]
},
{
"name": "Tue. 25 May 2021",
"year": 2021,
"month": 5,
"day": 25,
"breakdown": [
{
"name": "de",
"url": "",
"counts": [
"122863"
],
"breakdown": [
{
"name": "repeat-shopnow-191329",
"url": "",
"counts": [
"729"
]
},
{
"name": "shopnow\/kuendigungswiderruf\/bestellen",
"url": "https:\/\/www.mynet.de\/shopnow\/kuendigungswiderruf\/bestellen",
"counts": [
"330"
]
},
{
"name": "shopnow\/repeat-offer",
"url": "https:\/\/www.mynet.de\/shopnow\/repeat-offer",
"counts": [
"88"
]
},
{
"name": "shopnow\/repeat-technischer-fehler-rally",
"url": "https:\/\/www.mynet.de\/shopnow\/repeat-technischer-fehler-rally",
"counts": [
"54"
]
},
{
"name": "shopnow\/repeat-technischer-fehler-promo-data",
"url": "https:\/\/www.mynet.de\/shopnow\/repeat-technischer-fehler-promo-data",
"counts": [
"1"
]
}
],
"breakdownTotal": [
"122863"
]
},
{
"name": "at",
"url": "",
"counts": [
"9089"
],
"breakdown": [
{
"name": "shopnow\/repeat-offer",
"url": "https:\/\/www.mynet.de\/shopnow\/repeat-offer",
"counts": [
"1"
]
},
{
"name": "shopnow\/repeat-technischer-fehler-datenvalidierung",
"url": "https:\/\/www.mynet.de\/shopnow\/repeat-technischer-fehler-datenvalidierung",
"counts": [
"1"
]
},
{
"name": "shopnow\/kuendigungswiderruf-error-aex-eligible",
"url": "https:\/\/www.mynet.de\/shopnow\/kuendigungswiderruf-error-aex-eligible",
"counts": [
"1"
]
}
],
"breakdownTotal": [
"9089"
]
}
],
"breakdownTotal": [
"133319"
]
},
{
"name": "Wed. 26 May 2021",
"year": 2021,
"month": 5,
"day": 26,
"breakdown": [
{
"name": "de",
"url": "",
"counts": [
"105841"
],
"breakdown": [
{
"name": "shopnow\/repeat-technischer-fehler-paket-kombi",
"url": "https:\/\/www.mynet.de\/shopnow\/repeat-technischer-fehler-paket-kombi",
"counts": [
"9"
]
},
{
"name": "shopnow\/repeat-technischer-fehler-problem",
"url": "https:\/\/www.mynet.de\/shopnow\/repeat-technischer-fehler-problem",
"counts": [
"5"
]
},
{
"name": "shopnow\/repeat-technischer-fehler",
"url": "https:\/\/www.mynet.de\/shopnow\/repeat-technischer-fehler",
"counts": [
"3"
]
},
{
"name": "shopnow\/repeat-technischer-fehler-promo-data",
"url": "https:\/\/www.mynet.de\/shopnow\/repeat-technischer-fehler-promo-data",
"counts": [
"3"
]
},
{
"name": "shopnow\/kuendigungswiderruf-internal-error-read",
"url": "",
"counts": [
"2"
]
},
{
"name": "repeat-shopnow-190025",
"url": "",
"counts": [
"2"
]
},
{
"name": "shopnow\/kuendigungswiderruf-error-aex-eligible",
"url": "https:\/\/www.mynet.de\/shopnow\/kuendigungswiderruf-error-aex-eligible",
"counts": [
"2"
]
},
{
"name": "shopnow\/repeat-technischer-fehler-datenvalidierung",
"url": "https:\/\/www.mynet.de\/shopnow\/repeat-technischer-fehler-datenvalidierung",
"counts": [
"1"
]
}
],
"breakdownTotal": [
"105841"
]
},
{
"name": "at",
"url": "",
"counts": [
"6890"
],
"breakdown": [
{
"name": "shopnow\/kuendigungswiderruf\/bestellen",
"url": "https:\/\/www.mynet.de\/shopnow\/kuendigungswiderruf\/bestellen",
"counts": [
"12"
]
},
{
"name": "repeat-shopnow-206864",
"url": "https:\/\/www.mynet.at\/repeat-shopnow-206864",
"counts": [
"10"
]
},
{
"name": "shopnow\/repeat-offer",
"url": "https:\/\/www.mynet.de\/shopnow\/repeat-offer",
"counts": [
"8"
]
},
{
"name": "shopnow\/kuendigungswiderruf-error-aex-eligible",
"url": "https:\/\/www.mynet.de\/shopnow\/kuendigungswiderruf-error-aex-eligible",
"counts": [
"4"
]
},
{
"name": "shopnow\/repeat-technischer-fehler-promo-data",
"url": "https:\/\/www.mynet.de\/shopnow\/repeat-technischer-fehler-promo-data",
"counts": [
"1"
]
}
],
"breakdownTotal": [
"6890"
]
}
],
"breakdownTotal": [
"113662"
]
}
],
"totals": [
"380408"
],
"version": "1.4.18.10"
},
"waitSeconds": 0,
"runSeconds": 0
} -
Hi Spurthi!
Here is the solution you need -
To repeat and fill in the dates for each URL under breakdown/breakdown array you can use the following xpath.
kf:fill_elements(/report/data/breakdown/breakdown/name/../../..,'name'
To repeat the country id for each URL you can use the following xpath.
/report/data/breakdown/breakdown/following-sibling::name;And to select the URL you can use the following x-path.
@/report/data/breakdown/breakdown/name;
Please sign in to leave a comment.