Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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.
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
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}
];
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,