topic Re: Extract JSON Payment information from SQL DB in QlikView in App Development

Extract JSON Payment information from SQL DB in QlikView

johngouws — Wed, 30 Jul 2025 01:50:36 GMT

Good morning.
I am hoping someone is able to assist or advise me if it is possible to get data from a field that contains payment information.

I have data in a SQL DB and one on the fields, Payments, is either empty or has JSON as below.

I am trying to extract the Date and Amount portions. I found a couple of examples on the Community, but so far my attempts are not working out.

Looking at the below, is it all possible to extract the Date and Amount?

[
{
"PaymentID": "121d3a27-84be-416a-837c-6707d88ede6d",
"Date": "2025-07-30T02:00:00+02:00",
"Amount": 1300.0,
"Reference": "Payment",
"CurrencyRate": 1.0,
"HasAccount": false,
"HasValidationErrors": false
}
]

Thank you very much.

Re: Extract JSON Payment information from SQL DB in QlikView

Oleg_Troyansky — Wed, 30 Jul 2025 18:48:40 GMT

Hi John,

I suspect that the cool kids that know modern technologies could come up with some sort of a fancy Python function that would do it for you... I'm old school though, so if I had to do it, I'd simply use string functions like Mid() and Index() to calculate the appropriate substrings:

- Find the position of the word "Date" in the string
- Then, find the position of the first space after that
- Then, get the substring (mid() ) between that position and the position of the next comma
etc...

It will take a fair amount of tinkering, but overall it's not too complex to calculate these.

Cheers,

Oleg Troyansky

Re: Extract JSON Payment information from SQL DB in QlikView

Kaushik2020 — Wed, 06 Aug 2025 04:34:06 GMT

I had a similar scenario where i have parsed JSON format data in Qlik Sense. as we were using Microsoft SQL Server as backend, i have written a script to convert this JSON tags into column headers, further calling the column in Qlik Sense was quick for me.

Re: Extract JSON Payment information from SQL DB in QlikView

johngouws — Thu, 07 Aug 2025 11:39:31 GMT

Hi Oleg.

Thank you. I'm also from the same 'school', and I'm great full for it..

I've been fiddling with the string function and I think I get to what I am needing. A bit of trial and error wil do it.

Thank you.

Re: Extract JSON Payment information from SQL DB in QlikView

MarcoWedel — Fri, 08 Aug 2025 21:54:53 GMT

maybe helpful:

https://help.qlik.com/en-US/sense/May2025/Content/Sense_Helpsites/WhatsNew/What-is-new-May2025.htm?key=dc1da06fae2c45298321e25396536c72#:~:text=Native%20JSON%20Support%20for%20Qlik%20Sense