Loading a pipe-delimited text file with a separate data file via Qlik Sense

[jleefjcapital]

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

[petter]

‌Could you please explain what you mean by "Relational Database" .... Is it an associative database you are thinking about?

[jleefjcapital]

I think so.  I'm thinking of a standard, column and row format.  Is that associative? 

[JonnyPoole]

sure , when you go to load the text file, just switch the delimitter. A pipe is #127 in the ascii list of characters:

[petter]

So you have the column names in the schema file then and only the values for each column in the actual data file right?

[petter · Accepted solution]

This should work for you:

SchemaFile:

MAPPING LOAD

    '@' & FieldNo AS DataFileField,

    FieldName AS FinalFieldName

// FROM SchemaFile.TXT (delimiter is '|');

INLINE [

FieldNo|FieldName

1|TableName

2|Attribute

3|Value

4|Category

] (delimiter is '|');

DataFile:

LOAD

    *

// FROM DataFile.TXT (delimiter is '|' , no labels);

INLINE [

T1|color|red|a

T1|diameter|10 cm|b

T1|weight|100 g|c

T2|Color|black|d

T2|height|16 cm|e

T3|length|20 cm|f

T3|weight|500 g|g

T3|width|10 cm|h

] (delimiter is '|', no labels);

RENAME FIELDS USING SchemaFile;

[petter]

I didn't explain much - just ask if anything is unclear...

[jleefjcapital]

Thank you.  I will try this and let you know if I get stuck and if I'm successful!

[petter]

The comments // is what you should remove so you load from your files instead and then comment out lines 7-13 and 20-29 ...

[jleefjcapital]

Oh, I don't have to manually input the schema?   Whew.