Qualify multiple txt files into one table

[jozsefpeitli]

Dear All,

I am struggle with data load with qualify statment in QlikSense. Please check my problem and if you know how I should handle please let me know.

Please...

DataStructure:

Folder1

File1.txt

File2.txt

Folder2

File1.txt

File2.txt

Script1:

TableName1:

LOAD

Field1,

Field2,

Fieldn

FROM [lib://Folder1/*.txt]

(txt, codepage is 28591, embedded labels, delimiter is '\t', msq);

TableName2:

LOAD

Field1,

Field2,

Fieldn

FROM [lib://Folder2/*.txt]

(txt, codepage is 28591, embedded labels, delimiter is '\t', msq);

Output1:

TableName1:

All the files from both Folder loaded to this table.

Fields:

Field1,

Field2,

Fieldn

Script2:

Qualify *;

Unqualify Field1;

TableName1:

LOAD

Field1,

Field2,

Fieldn

FROM [lib://Folder1/*.txt]

(txt, codepage is 28591, embedded labels, delimiter is '\t', msq);

TableName2:

LOAD

Field1,

Field2,

Fieldn

FROM [lib://Folder2/*.txt]

(txt, codepage is 28591, embedded labels, delimiter is '\t', msq);

Unqualify*;

Output2:

TableName1:

The File1.txt from Folder1 loaded to this Table.

Fields:

Field1,

TableName1.Field2,

TableName1.Fieldn

TableName1-1:

The File2.txt from Folder1 loaded to this Table.

Fields:

Field1,

TableName1-1.Field2,

TableName1-1.Fieldn

TableName2:

The File1.txt from Folder2 loaded to this Table.

Fields:

Field1,

TableName2.Field2,

TableName2.Fieldn

TableName2-1:

The File2.txt from Folder2 loaded to this Table.

Fields:

Field1,

TableName2-1.Field2,

TableName2-1.Fieldn

WHAT I WANT output:

TableName1:

All files from Folder1 loaded to this Table.

Fields:

Field1,

TableName1.Field2,

TableName1.Fieldn

TableName2:

All files from Folder2 loaded to this Table.

Fields:

Field1,

TableName2.Field2,

TableName2.Fieldn

I made 3 images to help understand What will be the ideal output.

Script 1 output using one of the field:

Ideal output:

!

Thank you!

Regards

Jozsef

[marcus_sommer]

Pesonally I wouldn't use qualify in such a case else I would add a filename() or filebasename() within the loads to be able to see which data are from which source.

- Marcus

[jozsefpeitli]

Dear Marcus,

Thank you for your quick answer.

I made some runs with your suggestion, but I do not like the result.

Still I get only one table with all the files from both folder.

If I use one of the field (during the visualization) it is still contain all the data from all the folders.

What I need:

If I use one of the field (during the visualization) I need the data only from one folder.

Sorry for my bad English. I hope it is clear enough

BR

Jozsef

[JustinDallas]

What is the business objective?  When I am loading data from multiple files that have similar names, I generally create a flag to tell me where a particular data row came from.

[marcus_sommer]

If the fields contain the same kind of data I would in generally remain by my sugestion maybe by adding another more general source field like: 'Folder 1' as Source and then using this field for appropriate selections or within a set analysis like: sum({< Source = {'Folder 1'}>} Value).

If the kind of data are different in these folders I would rename them manually to something useful but not using qualify.

- Marcus

[jozsefpeitli]

Dear Justin,

The Files came from an automatic measuring system.

The folders mean slightly different products, the files store many individual measures. I would like to compare the data.

I hope it will help.

Jozsef

[jozsefpeitli]

Dear Marcus,

I am afraid, the solution will be the manual one...

😞

BR

Jozsef

[marcus_sommer · Accepted solution]

I think you mustn't be sorry about to (re)name all fields and tables explicitely. It's the best way to ensure that you know and understand your data and that the datamodel is build properly.

Of course you could use qualify but it has often far more disadvantages than benefits and therefore my suggestion of not using it in your descriped case.

- Marcus

[jozsefpeitli]

Dear Marcus,

Thank you for your Time, I will rename the fields manually.

Best Regards

Jozsef