topic Re: Separating paragraph data as id's and create Row in QlikView

Separating paragraph data as id's and create Row

geetha_n — Wed, 25 Nov 2020 16:16:04 GMT

Example of data: Has to automated through QlikView

Thanks in advance, Please help, any suggestions, references are fine

8050745	863283 SureSigns VS4 NBP, SPO2 863286 SureSigns VS4 Government Bundle 863340 SureSigns VS4 Government Bundle 863372 Gov Bundle - VS4, etCO2, Predictive Temp 863373 Gov Bundle - VS4, etCO2, Temporal Temp	PM-Suresigns
8113170	861388 HeartStart FR3 Defibrillator, ECG	AED Bothell
8113170	861389 HeartStart FR3 Defibrillator, Text	AED Bothell

The result has to be like this:

8050745	863283	PM-Suresigns
8050745	863286	PM-Suresigns
8050745	863340	PM-Suresigns
8050745	863372	PM-Suresigns
8050745	863373	PM-Suresigns
8113170	861388	AED Bothell
8113170	861389	AED Bothell

Re: Separating paragraph data as id's and create Row

vishsaggi — Mon, 23 Jul 2018 15:19:51 GMT

Use subfield function to get the numbers like below:

If this is not you are looking for let us know in more detail how your raw data comes a sample excel file will help to looki into.

LOAD *,

Subfield(Yourfieldname, ' ', 1) AS ID

FROM yourtableName;

Re: Separating paragraph data as id's and create Row

sunny_talwar — Mon, 23 Jul 2018 15:23:29 GMT

What is the logic for the third column?

Re: Separating paragraph data as id's and create Row

geetha_n — Tue, 24 Jul 2018 05:29:14 GMT

Third Filed is the other column, does not belongs to it

Re: Separating paragraph data as id's and create Row

geetha_n — Tue, 24 Jul 2018 05:34:30 GMT

If you scroll the source data there is a third column.

Re: Separating paragraph data as id's and create Row

geetha_n — Tue, 24 Jul 2018 05:38:04 GMT

Thanks for the answer @Vishwarath Nagaraju, But there are multiple lines, from 2nd line also I have to get the numbers.

I thought to put it in a loop, Is it possible to use a loop the line which I has highlighted below?

I have used the load like below

Data1:

LOAD [Trackwise #] as Track_no,

[Material],

left(Material,index([Material],' ')) as Material_Id,

dis as Discription

FROM

(ooxml, embedded labels, header is 6 lines, table is [Page1])

where IsNum([Trackwise #]);

Re: Separating paragraph data as id's and create Row

sunny_talwar — Tue, 24 Jul 2018 12:01:54 GMT

Try this

Table:

LOAD Field1,

SubField(Field2, ' ', 1) as Field2,

Field3;

LOAD Field1,

SubField(Field2, Chr(10)) as Field2,

Field3

FROM

[..\..\Downloads\308666.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: Separating paragraph data as id's and create Row

vishsaggi — Tue, 24 Jul 2018 15:08:07 GMT

Not sure what you mean looping through? You still having issues with Subfield() ?.