Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

evgeniystuchalk
Contributor II

Take value from text: random value position

Hi! I have table with calls. All information about call keeps in text cell, like

{"PHONE":"84955438307","DURATION":"57","SRC":"","UNIQ":"1448962343"}

I need to take duration as a number. How I can extract it from text?

I can predict position of DURATION, because most of calls have this kind of data in text cell:

{"UNIQ":"62c871ef-c0de-4d76-af2d-d1f9413cceaf","LINK":"https://rcsgroup.onpbx.ru/download_amocrm/eyJ1IjoiNjJjODcxZWYtYzBkZS00ZDc2LWFmMmQtZDFmOTQxM2NjZWFmIi...","PHONE":"74950550244","DURATION":41,"from":"+74950550244","extinfo":"","SRC":"teslatelecom"}

1 Reply
Employee
Employee

Re: Take value from text: random value position

This expression will :

- take the text between the first and second commas

- from that text, remove the double quote characters

- of the remaining text, take the characters after the comma

mid(

  purgechar( mid(Call, index(Call,',',1)+1, index(Call,',',2) - index(Call,',',1) - 1), '"'),

    index( purgechar( mid(Call, index(Call,',',1)+1, index(Call,',',2) - index(Call,',',1) - 1), '"'),':')+1

)

You could use it on a load script as well. Read the assumptions above carefully and employ only if these assumptions are correct with regards to the possible data values. It may be better to tweak the expression to be more resilient... but again that depends on the possible data values.

Capture.PNG

Community Browser