topic Re: Split fields from fields in a text line in QlikView

Split fields from fields in a text line

Anonymous — Fri, 20 Apr 2018 12:08:50 GMT

Hi,

I have the following Problem.

Normally, the field names an data in a file look like that:

ID: 1

Date: 20.04.2018

Item1: Content1

Item2: Content2

ID: 2

Date: 21.04.2018

Item1: Content1

Item2: Content2

...

But in many cases it looks like that:

ID: 1

Date: 20.04.2018

Item1: Content1 Item2: Content2

ID: 2

Date: 21.04.2018

Item1: Content3 Item2: Content4

The result should be:

ID	Date	Item1	Item2
1	20.04.2018	Content1	Content2
2	21.04.2018	Content3	Content4

Does anyone has an idea, how to solve the problem?

Best regards

Michael

Re: Split fields from fields in a text line

sunny_talwar — Fri, 20 Apr 2018 12:09:52 GMT

Did you look at the response here?

Re: Data Transformation

Re: Split fields from fields in a text line

Anonymous — Fri, 20 Apr 2018 12:12:02 GMT

Yes, I've looked at it and the problem is solved. But this is an additional Problem from a different file with that slightly different structure of data.

Re: Split fields from fields in a text line

sunny_talwar — Fri, 20 Apr 2018 12:13:38 GMT

I don't see much difference from the previous thread.... is this data coming from Excel file?

Re: Split fields from fields in a text line

Anonymous — Fri, 20 Apr 2018 12:16:48 GMT

No, its an exctract from a PDF. The difference is, that you have some of the fieldnames and the content of that fields are comming from one text line, whereas in the the former file each fieldname/content has its separate line.

Re: Split fields from fields in a text line

sunny_talwar — Fri, 20 Apr 2018 12:25:05 GMT

Oh, so you want to know how to split them in two columns... try SubField for that

Table:

LOAD *,

PurgeChar(Field, ':') as NewField,

If(Field = 'ID', Data, Peek('RowNum')) as RowNum;

LOAD Trim(SubField(F1, ':', 1)) as Field,

Trim(SubField(F1, ':', 2)) as Data;

LOAD * INLINE [

ID: 1

Date: 20.04.2018

Item1: Content1

Item2: Content2

ID: 2

Date: 21.04.2018

Item1: Content1

Item2: Content2

];

FinalTable:

LOAD Distinct RowNum

Resident Table;

FOR i = 1 to FieldValueCount('NewField')

LET vField = FieldValue('NewField', $(i));

Left Join (FinalTable)

LOAD RowNum,

Data as [$(vField)]

Resident Table

Where NewField = '$(vField)';

NEXT

DROP Table Table;

Re: Split fields from fields in a text line

Anonymous — Fri, 20 Apr 2018 12:34:04 GMT

Thank you, but the structure of the input data is a little bit different.

It's not:

ID: 1

Date: 20.04.2018

Item1: Content1

Item2: Content2

ID: 2

Date: 21.04.2018

Item1: Content1

Item2: Content2

but

ID: 1

Date: 20.04.2018

Item1: Content1 Item2: Content2

ID: 2

Date: 21.04.2018

Item1: Content1 Item2: Content2

The information of two fields is put in one input line.

Item1: Content1 Item2: Content2

So QV is interpreting the line as:

Fieldname: Item1

Content: Content1 Item2: Content2

So the second fieldname/content is "hidden" in the content of the first field.

Re: Split fields from fields in a text line

sunny_talwar — Fri, 20 Apr 2018 12:35:05 GMT

oh okay, I got it now... sorry for the confusion... checkin

Re: Split fields from fields in a text line

sunny_talwar — Fri, 20 Apr 2018 12:49:18 GMT

May be like this

Table:

LOAD *,

PurgeChar(Field, ':') as NewField,

If(Field = 'ID', Data, Peek('RowNum')) as RowNum;

LOAD Trim(SubField(F1, ':', 1)) as Field,

Trim(SubField(F1, ':', 2)) as Data

Where Len(Trim(SubField(F1, ':', 2))) > 0;

LOAD If(Index(F1, 'Item'), 'Item' & SubField(F1, 'Item'), F1) as F1;

LOAD * INLINE [

ID: 1

Date: 20.04.2018

Item1: Content1 Item2: Content2

ID: 2

Date: 21.04.2018

Item1: Content1 Item2: Content2

];

FinalTable:

LOAD Distinct RowNum

Resident Table;

FOR i = 1 to FieldValueCount('NewField')

LET vField = FieldValue('NewField', $(i));

Left Join (FinalTable)

LOAD RowNum,

Data as [$(vField)]

Resident Table

Where NewField = '$(vField)';

NEXT

DROP Table Table;

Re: Split fields from fields in a text line

Anonymous — Fri, 20 Apr 2018 13:02:59 GMT

That looks great, but what to do if the fieldnames are not nearly identical (item1, item2) but e.g. "item" and "color"

Re: Split fields from fields in a text line

sunny_talwar — Fri, 20 Apr 2018 13:07:41 GMT

See all these are work arounds as there is no easy way to pull the data... now if I give you a solution which works for this, it may break something else. To help it make it easy, would you be able to share a sample which is very close to your real scenario... otherwise we will just be doing this over and over and over again....

Re: Split fields from fields in a text line

Anonymous — Mon, 23 Apr 2018 06:10:43 GMT

Hi Sunny, !

sorry, you're completly right. I've attached a file created from the original data. I've only "neutralized" the content.

Michael

Re: Split fields from fields in a text line

sasiparupudi1 — Mon, 23 Apr 2018 07:27:18 GMT

Your test file contains headers and tables.. which section you want us to help you transform.?

Please provide a clean sample.

Re: Split fields from fields in a text line

Anonymous — Mon, 23 Apr 2018 07:31:15 GMT

The file contains only 1 table. And what do you mean by headers. The data in the file have the structure already discussed with Sunny Talwar.

Re: Split fields from fields in a text line

sasiparupudi1 — Mon, 23 Apr 2018 07:34:51 GMT

The file contains balnk line and tables








			[°]	[°C]
1	2893	12:19:55	28,192	1,999
2	2894	12:20:26	---	---
Mittelwert	2892	12:20:27	-	-
GxP Information (für 589 nm):
Letzte Quartz Justierung: 23.08.2017 11:26:56 von Service
Letzter Check (Quarzcheck): 23.08.2017 10:23:20 von Service - Bestanden
Elektronische Unterschrift der Probe:
Autor: Nicht unterzeichnet Prüfer: Nicht unterzeichnet Freigeber: Nicht unterzeichnet
Mittwoch, 6. Dezember 2017 \| 18:18:35
(Unterschrift)
Benutzer: Remote
Nominale Messzellenlänge: 100,00 mm
Messergebnis:
Teilmessungsnummer	Eindeutige Proben-ID	Zeit	Opt. Drehung (längen-korrigiert)		Messzellentemperatur	Reale Messzellenlänge (TC)
			[°]		[°C]	[mm]
1	2896	10:21:00	66,364		2,009	1,000,082
2	2897	10:21:29	66,365		2,002	1,000,081
3	2898	10:21:57	66,366		1,997	1,000,081
Mittelwert	2895	10:21:58	66,365		2,003	1,000,081
Std.-Abw.			82		492	47
GxP Information (für 589 nm):
Letzte Quartz Justierung: 23.08.2017 11:24:47 von Service
Letzter Check (Quarzcheck): 31.08.2017 10:04:12 von phvle0 - Bestanden
Elektronische Unterschrift der Probe:
Autor: Nicht unterzeichnet Prüfer: Nicht unterzeichnet Freigeber: Nicht unterzeichnet
Mittwoch, 6. Dezember 2017 \| 18:18:35
(Unterschrift)

Re: Split fields from fields in a text line

Anonymous — Mon, 23 Apr 2018 07:40:27 GMT

Please remove the table Information from your post. That seems to be the wrong Information.

Re: Split fields from fields in a text line

sunny_talwar — Mon, 23 Apr 2018 09:09:37 GMT

Don't see anything attached