Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need to build a relational database using a schema file and the actual data file that are both pipe delimited text files. Does anyone know how I can go about doing this?
Thank you,
Jessica
The file is rather large. May I provide you with my email address so that you can email me your email address? I can email it to you. Thank you for your help on this, and your patience. jlee@fjcapital.com
Hi Petter,
Your code worked! However, when I use two codes (for different files), I get an error. I tried slightly changing the reference table name of the second load statement, but Qlik is having a hard time finding it. However, it does work beautifully when I pull one at a time.
Am I missing something?
[SCHEMA]:
LOAD
[Ordinal],
'@' & ([Ordinal]+1) AS %Key,
[GUID],
[KeyItem],
[SNLxl KeyField],
[ProductCaption],
[DataType],
[Magnitude],
[Length]
FROM [lib://NEW_CBSB_Corporate_1_SCHEMA.txt]
(txt, codepage is 1252, embedded labels, delimiter is '|', no quotes, no eof);
[MAPPING_SCHEMA]:
MAPPING LOAD
'@' & (Ordinal+1) AS FieldNum,
ProductCaption AS NewFieldName
RESIDENT
SCHEMA
;
[CBSB_CORPORATE]:
FIRST 1000
LOAD
*
FROM [lib://NEW_CBSB_Corporate_20150525_1.txt]
(txt, utf8, no labels, delimiter is '|', no quotes);
RENAME FIELDS USING [MAPPING_SCHEMA];
/////////// //// Data file 2
[SCHEMA1]:
LOAD
[Ordinal],
'@' & ([Ordinal]+1) AS %Key,
[GUID],
[KeyItem],
[SNLxl KeyField],
[ProductCaption],
[DataType],
[Magnitude],
[Length]
FROM [lib://NEW_CBSB_BasicFinl1_1_SCHEMA.txt]
(txt, codepage is 1252, embedded labels, delimiter is '|', no quotes, no eof);
[MAPPING_SCHEMA1]:
MAPPING LOAD
'@' & (Ordinal+1) AS FieldNum,
ProductCaption AS NewFieldName
RESIDENT
SCHEMA1
;
[CBSB_BasicFinl1]:
FIRST 1000
LOAD
*
FROM [lib://NEW_CBSB_BasicFinl1_20150525_1.txt]
(txt, utf8, no labels, delimiter is '|', no quotes);
RENAME FIELDS USING [MAPPING_SCHEMA1];