Extract JSON Payment information from SQL DB in QlikView

[johngouws]

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. 

[Oleg_Troyansky · Accepted solution]

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

[Kaushik2020]

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. 

[johngouws]

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. 

[MarcoWedel]

maybe helpful:

https://help.qlik.com/en-US/sense/May2025/Content/Sense_Helpsites/WhatsNew/What-is-new-May2025.htm?k...