Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Data format change from DB

Hey Techies,

I have a data like attached below and exported this from a Postgresql Database.

Silambarasan1306_0-1590134891695.png

Is there any way to split this data into multiple colums..?? delimiter is ','.

Need immediate help.

Thanks.

5 Replies
Highlighted
Specialist
Specialist

Re: Data format change from DB

Hi @Silambarasan1306 

Yes, it is possible, your screenshot show a Json string, Can you create an Excel file with 10 sample records? It is a parsing exercise 

hth

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.
Creator III
Creator III

Re: Data format change from DB

Hi @ArnadoSandoval ,

Data is readed from DB..

If we create the same in excel we can split the records into column using delimiter..

My question here is, if we read from DB or after converted qvd.. can we split the data into columns..??

 

Thanks.

Highlighted
Specialist
Specialist

Re: Data format change from DB

@Silambarasan1306 

Yes, it is possible in your Load Script, as I explained in this topic Field Parsing; Qlik has function to split the data, but it is better implemented by the load script; I am trying to find an example file with Json fields like yours, I will get back to your question if I find something.

hth

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.
Highlighted
Creator III
Creator III

Re: Data format change from DB

Hi @ArnadoSandoval ,

PFA sample data below.

Load * Inline [

DataField

{"sn": "CXNK005A7A37", "_id": "36721275-041d-419f-bfcf-a2938bd3ebc0", "orgId": "470053", "testId": "74331548-b349-4992-b41d-77333e3759c6", "timestamp": {"$date": "2019-03-13T11:01:03.015Z"}, "statusCode": 0, "ooklaServer": "10395", "uploadSpeed": 79790, "downloadSpeed": 10134}

{"sn": "CXNK005764A0", "_id": "91033d4e-b36a-432c-8c29-fd18768025db", "orgId": "CSC", "testId": "bbf87d70-b18c-449a-a41f-58c298762a54", "timestamp": {"$date": "2019-05-08T18:43:59.504Z"}, "statusCode": 0, "ooklaServer": "seattle.speedtest.bluespanwireless.com", "uploadSpeed": 62398, "downloadSpeed": 41147}

];

Highlighted
Specialist
Specialist

Re: Data format change from DB

Hi @Silambarasan1306 

The script below Parses Json strings, it is custom made for your Json's structure, it does not fully parse the timestamp field, you could extend the script to handle it. These are the Qlik Sense functions required to implement the parsing:

The Script:

NoConcatenate

JSonFields:
Load * Inline [
Json_Token
sn
_id
orgId
testId
timestamp
statusCode
ooklaServer
uploadSpeed
downloadSpeed
];

Map_JSonFields:
Mapping Load
    Json_Token             As Key,
    '<' & Json_Token & '>' As Value
Resident JSonFields;

NoConcatenate

ParsingV4_Json:
Load 
     Record,
     DataField,
     NewDataField,
     Replace(SubField(SubField(SubField(sn,            '<', 2), ':', 2), ',', 1), Chr(34), '') As sn,
     Replace(SubField(SubField(SubField(_id,           '<', 2), ':', 2), ',', 1), Chr(34), '') As _id,
     Replace(SubField(SubField(SubField(orgId,         '<', 2), ':', 2), ',', 1), Chr(34), '') As orgId,
     Replace(SubField(SubField(SubField(testId,        '<', 2), ':', 2), ',', 1), Chr(34), '') As testId,
     SubField(SubField(timestamp,     '<', 2), '>', 2) As timestamp,
     SubField(SubField(SubField(statusCode,    '<', 2), ':', 2), ',', 1) As statusCode,
     Replace(SubField(SubField(SubField(ooklaServer,   '<', 2), ':', 2), ',', 1), Chr(34), '') As ooklaServer,
     SubField(SubField(SubField(uploadSpeed,   '<', 2), ':', 2), ',', 1) As uploadSpeed,
     SubField(SubField(SubField(downloadSpeed, '<', 2), ':', 2), '}', 1) As downloadSpeed
;
Load *,
     If(Index(NewDataField, '<sn') = 0, '>',
        Mid(NewDataField, Index(NewDataField, '<sn')))            As sn,
     If(Index(NewDataField, '<_id') = 0, '>',
        Mid(NewDataField, Index(NewDataField, '<_id')))           As _id,
     If(Index(NewDataField, '<orgId') = 0, '>',
        Mid(NewDataField, Index(NewDataField, '<orgId')))         As orgId,
     If(Index(NewDataField, '<testId') = 0, '>',
        Mid(NewDataField, Index(NewDataField, '<testId')))        As testId,
     If(Index(NewDataField, '<timestamp') = 0, '>',
        Mid(NewDataField, Index(NewDataField, '<timestamp')))     As timestamp,
     If(Index(NewDataField, '<statusCode') = 0, '>',
        Mid(NewDataField, Index(NewDataField, '<statusCode')))    As statusCode,
     If(Index(NewDataField, '<ooklaServer') = 0, '>',
        Mid(NewDataField, Index(NewDataField, '<ooklaServer')))   As ooklaServer,
     If(Index(NewDataField, '<uploadSpeed') = 0, '>',
        Mid(NewDataField, Index(NewDataField, '<uploadSpeed')))   As uploadSpeed,
     If(Index(NewDataField, '<downloadSpeed') = 0, '>',
        Mid(NewDataField, Index(NewDataField, '<downloadSpeed'))) As downloadSpeed
;
Load *,
     MapSubString('Map_JSonFields', DataField) As NewDataField 
;
Load RecNo() As Record, DataField Inline [
DataField
{"sn": "CXNK005A7A37", "_id": "36721275-041d-419f-bfcf-a2938bd3ebc0", "orgId": "470053", "testId": "74331548-b349-4992-b41d-77333e3759c6", "timestamp": {"$date": "2019-03-13T11:01:03.015Z"}, "statusCode": 0, "ooklaServer": "10395", "uploadSpeed": 79790, "downloadSpeed": 10134}
{"sn": "CXNK005764A0", "_id": "91033d4e-b36a-432c-8c29-fd18768025db", "orgId": "CSC", "testId": "bbf87d70-b18c-449a-a41f-58c298762a54", "timestamp": {"$date": "2019-05-08T18:43:59.504Z"}, "statusCode": 0, "ooklaServer": "seattle.speedtest.bluespanwireless.com", "uploadSpeed": 62398, "downloadSpeed": 41147}
] (delimiter is '|');

Hope this helps,

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.