Formatting of Data

[anwar_pardawala]

Hi,

I have Following Data from IT in Txt file which i need to use to create Dashboard the Columns are "|"(pipe separated)  the issue is Data is not in unusual format and when i extract data from text File instead of creating 6 columns Data goes into different rows.

I have Highlighted what should come as different columns, is there any solution for this?

1|06269051-df49-4034-8d3e-b3d980db95f3|c95b0b52-a45b-4194-acdf-ae8bfe94dae7|{

"pnc" : {

    "reasonCode" : "5",

    "location" : "Test",

  },

  "common" : {

    "name" : {

      "surname" : "Avey",

      "forename1" : "Lee"

    },

         "reasonCode" : "5",

      "location" : "Test",

         } ] ]

  }

}|REQUEST|0.83.0

2|128ce879-3c00-485b-9ed1-8c1ad53dbd4d|c95b0b52-a45b-4194-acdf-ae8bfe94dae7|[ "The following server backends reported an error: PNC" ]|RESPONSE|0.83.0

3|48d7fb39-a3ae-460b-afdb-ac71a225a39e|d6f96fbb-e69e-495b-8cbd-e117e44b562a|{

  "pnc" : {

    "reasonCode" : "5",

    "location" : "Test",

  },

  "common" : {

    "name" : {

      "surname" : "Avey",

      "forename1" : "Lee"

    },

         "reasonCode" : "5",

      "location" : "Test",

     } ] ]

  }

}|REQUEST|0.83.0

4|4e6c205a-5920-4492-a3ff-5ad5aa797ca6|d6f96fbb-e69e-495b-8cbd-e117e44b562a|[ "The following server backends reported an error: PNC" ]|RESPONSE|0.83.0

5|9d4e8084-1780-4bb8-9788-b80c74b4ad8b|85863418-67c3-41a6-bf75-e374017612cb|{

  "pnc" : {

    "reasonCode" : "5",

    "location" : "Test",

  },

  "common" : {

    "vrm" : "Rk09xnj",

      "reasonCode" : "5",

      "location" : "Test",

    } ] ]

  }

}|REQUEST|0.83.0

6|19b282fa-3709-4c77-8439-9ca24cb3755a|85863418-67c3-41a6-bf75-e374017612cb|[ "The following server backends reported an error: PNC" ]|RESPONSE|0.83.0

[sudeepkm]

you may need to check this post.

How to convert JSON Source Data to XML Data in QlikView.

[rubenmarin]

Hi Anwar, you can try subfield funcion:

Subfield(textField,'|',1) as FieldColumn1,

Subfield(textField,'|',2) as FieldColumn2,

...

[anwar_pardawala]

Sudeep, this is an txt file not a JSON.

[anwar_pardawala]

Ruben, For me to use the script i need to load the data first, but as soon as i load it it goes into separate rows.

[Or]

You can't natively load a file with both a separator and carriage return / line feeds contained within the row values.

Have a look at this workaround: https://community.qlik.com/docs/DOC-4716

Alternatively, remove the line breaks (whichever type they may be) using an external option, e.g. by replacing them in the underlying query.

[lorenzoconforti]

I don't think you can remove the unwanted line breaks easily when you import the file. The only thing I can suggest is to parse the file and create an extra row of data when you have counted 5 pipes. See below script; I've tested it with the data you've provided and it works fine (although not very elegant)

Data1:

LOAD @1 as Data,  SubStringCount(@1, '|') as Pipe

FROM

testdata.txt

(txt, codepage is 1252, no labels, delimiter is ',', no quotes);

Let vRows= NoOfRows('Data1');

let vPipeN = 0;

let vRowData =;

For j=0 to (vRows - 1)

let vCurrentRow =;

vCurrentRow = Peek('Data',j,'Data1') ;

vRowData = '$(vRowData)' & '$(vCurrentRow)';

vPipeN = $(vPipeN) + Peek('Pipe',j,'Data1');

IF ($(vPipeN) = 5) then //create a new row

Data2:

Load

'$(vRowData)' as Data2

AutoGenerate 1;

vPipeN = 0;

vRowData = ;

ENDIF

Next j;

drop Table Data1;

FinalData:

load SubField(Data2, '|',1) as Field1,

SubField(Data2, '|',2) as Field2,

SubField(Data2, '|',3) as Field3,

SubField(Data2, '|',4) as Field4,

SubField(Data2, '|',5) as Field5

Resident Data2;

drop table Data2;

[anwar_pardawala]

Thanks Lorenzo Will try this as well. Seems to be a simpler solution than what i have done .