Data format change from DB

[Silambarasan1306]

Hey Techies,

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

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

Need immediate help.

Thanks.

[ArnadoSandoval]

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

[Silambarasan1306]

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.

[ArnadoSandoval]

@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

[Silambarasan1306]

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}

];

[ArnadoSandoval]

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:

• MapSubstring 
• Index 
• Mid 
• SubField 
• Replace 

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,