topic Re: Formatting of Data in QlikView

Formatting of Data

anwar_pardawala — Tue, 16 Oct 2018 15:50:16 GMT

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

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

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

Re: Formatting of Data

sudeepkm — Tue, 16 Oct 2018 16:04:57 GMT

you may need to check this post.

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

Re: Formatting of Data

rubenmarin — Tue, 16 Oct 2018 16:05:45 GMT

Hi Anwar, you can try subfield funcion:

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

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

...

Re: Formatting of Data

anwar_pardawala — Thu, 18 Oct 2018 09:53:57 GMT

Sudeep, this is an txt file not a JSON.

Re: Formatting of Data

anwar_pardawala — Thu, 18 Oct 2018 09:54:53 GMT

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.

Re: Formatting of Data

Or — Thu, 18 Oct 2018 11:39:59 GMT

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.

Re: Formatting of Data

lorenzoconforti — Thu, 18 Oct 2018 12:12:28 GMT

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;

Re: Formatting of Data

anwar_pardawala — Mon, 05 Nov 2018 14:51:05 GMT

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