Reformatting "unstructured" data issue

Thu, 08 Oct 2015 22:14:31 GMT

Ok,

I'm trying to process data, where each "row" in my text file is JSONish formatted data as shown below (data renamed for security):

{"val":"dummy","val1":"dummy1","val2":"dummy2","val3":"dummy3"}

I have several issues trying to process this data into Qlikview.

1. The order of the fields is not consistent from row to row (I could see val1 first, or val3 first etc.)

2. The number of fields varies from record to record (I have rows than span from 12 fields to 30 fields)

3. The first part of the data before the : needs to become the Field name.

In my load script I've managed to separate the data into "fields" of "val":"dummy" etc., but now I'm stumped as to how to process it all into proper field names. I can't run a loop inside a load statement, and I've not found a way to apply a map that converts the field from "val1":"dummy1" to a field name of "val1" with a value of "dummy1". Any suggestions?

InitialLoad:

LOAD Autonumber(@2) AS Index, @2 AS Data, SubStringCount(@2,',') AS FieldCount

FROM

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

SplitFields1:

LOAD *,

Subfield(Data, ',', 1) AS FieldAA,

Subfield(Data, ',', 2) AS FieldAB,

Subfield(Data, ',', 3) AS FieldAC,

Subfield(Data, ',', 4) AS FieldAD,

Subfield(Data, ',', 5) AS FieldAE,

Subfield(Data, ',', 6) AS FieldAF,

Subfield(Data, ',', 7) AS FieldAG,

Subfield(Data, ',', 😎 AS FieldAH,

Subfield(Data, ',', 9) AS FieldAI,

Subfield(Data, ',', 10) AS FieldAJ,

Subfield(Data, ',', 11) AS FieldAK,

Subfield(Data, ',', 12) AS FieldAL,

Subfield(Data, ',', 13) AS FieldAM,

Subfield(Data, ',', 14) AS FieldAN,

Subfield(Data, ',', 15) AS FieldAO,

Subfield(Data, ',', 16) AS FieldAP,

Subfield(Data, ',', 17) AS FieldAQ,

Subfield(Data, ',', 18) AS FieldAR,

Subfield(Data, ',', 19) AS FieldAS,

Subfield(Data, ',', 20) AS FieldAT,

Subfield(Data, ',', 21) AS FieldAU,

Subfield(Data, ',', 22) AS FieldAV,

Subfield(Data, ',', 23) AS FieldAW,

Subfield(Data, ',', 24) AS FieldAX,

Subfield(Data, ',', 25) AS FieldAY,

Subfield(Data, ',', 26) AS FieldAZ,

Subfield(Data, ',', 27) AS FieldBA,

Subfield(Data, ',', 28) AS FieldBB,

Subfield(Data, ',', 29) AS FieldBC,

Subfield(Data, ',', 30) AS FieldBD

Resident InitialLoad

;

DROP Table InitialLoad

Re: Reformatting "unstructured" data issue

sunny_talwar — Thu, 08 Oct 2015 23:17:06 GMT

You said its a text file right???

May be this code:

Table:

LOAD @1,

@2,

@3,

@4,

@5,

@6,

FROM

Community_166086.txt

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

Output in a text box object:

Re: Reformatting "unstructured" data issue

nagaiank — Fri, 09 Oct 2015 00:08:54 GMT

You may use generic load for this. For the input data

You can get the following result

The application and the data file to load are attached.

Re: Reformatting "unstructured" data issue

Fri, 09 Oct 2015 00:15:23 GMT

That's getting close to what I need. I think I misstated my critical need. Here's the format that I need to make it work properly:

Also, for some reason the license I have from my company does not allow me to open your QVW.

Re: Reformatting "unstructured" data issue

nagaiank — Fri, 09 Oct 2015 00:29:22 GMT

If you display the data values as pivot table, you will get what is shown below:

No clue about your inability to open the qvw file. I am using 64-bit QV11.2 SR10 on Windows 8.1

The modified application with the pivot table is attached.

topic Reformatting "unstructured" data issue in QlikView

Reformatting "unstructured" data issue

Re: Reformatting "unstructured" data issue

Re: Reformatting "unstructured" data issue

Re: Reformatting "unstructured" data issue

Re: Reformatting "unstructured" data issue