How to load multiline tagged field records

Thu, 28 Sep 2017 10:36:05 GMT

I can't work out how to load this file:

--------------------------------------------------------

JrId: 1

JournalTitle: AADE editors' journal

MedAbbr: AADE Ed J

ISSN (Print): 0160-6999

ISSN (Online):

IsoAbbr: AADE Ed J

NlmId: 7708172

--------------------------------------------------------

JrId: 2

JournalTitle: AANA journal

MedAbbr: AANA J

ISSN (Print): 0094-6354

ISSN (Online):

IsoAbbr: AANA J

NlmId: 0431420

--------------------------------------------------------

JrId: 3

JournalTitle: AARN news letter

MedAbbr: AARN News Lett

ISSN (Print): 0001-0197

ISSN (Online):

IsoAbbr: AARN News Lett

NlmId: 1251052

--------------------------------------------------------

ftp://ftp.ncbi.nih.gov/pubmed/J_Entrez.txt

I can connect and load it, by switching off Anonymous and using explicit anonymous login with email address as password, but can't work out how to parse it. It's a fixed 8-line structure, but I can't see a load option that works. I see field-tagged formats like this all the time in bibliographic work, but never tried loading one into Qlik before.

Any ideas?

Andy

Re: How to load multiline tagged field records

marcus_sommer — Thu, 28 Sep 2017 15:08:38 GMT

You could use peek() to access on the records and subfield() to separate the value from the fieldname, for example in this way:

t1:

load *, rowno() as RowNo, ceil(rowno() / 7) as ID, if(mod(rowno(), 7) = 0, 1, 0) as Filter inline [

Data

--------------------------------------------------------

JrId: 1

JournalTitle: AADE editors' journal

MedAbbr: AADE Ed J

ISSN (Print): 0160-6999

ISSN (Online):

IsoAbbr: AADE Ed J

NlmId: 7708172

--------------------------------------------------------

JrId: 2

JournalTitle: AANA journal

MedAbbr: AANA J

ISSN (Print): 0094-6354

ISSN (Online):

IsoAbbr: AANA J

NlmId: 0431420

--------------------------------------------------------

JrId: 3

JournalTitle: AARN news letter

MedAbbr: AARN News Lett

ISSN (Print): 0001-0197

ISSN (Online):

IsoAbbr: AARN News Lett

NlmId: 1251052

--------------------------------------------------------

] where left(Data, 3) <> '---' and len(Data) >= 1;

t2:

load RowNo, ID, Filter, Data,

peek('Data', -6) as JrId, peek('Data', -5) as JournalTitle,

peek('Data', -4) as MedAbbr, peek('Data', -3) as [ISSN (Print)],

peek('Data', -2) as [ISSN (Online)], peek('Data', -1) as IsoAbbr,

Data as NlmId

resident t1 order by RowNo;

t3:

noconcatenate

load ID,

subfield(JrId, ':', 2) as JrId, subfield(JournalTitle, ':', 2) as JournalTitle,

subfield(MedAbbr, ':', 2) as MedAbbr,

subfield([ISSN (Print)], ':', 2) as [ISSN (Print)], subfield([ISSN (Online)], ':', 2) as [ISSN (Online)],

subfield(IsoAbbr, ':', 2) as IsoAbbr,

subfield(NlmId, ':', 2) as NlmId

resident t2 where Filter = 1;

drop tables t1, t2;

- Marcus

topic Re: How to load multiline tagged field records in QlikView

How to load multiline tagged field records

Re: How to load multiline tagged field records