Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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