Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
jozsefpeitli
New Contributor II

Qualify multiple txt files into one table

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:

current.jpg

Ideal output:

wish.jpgwish2.jpg !

Thank you!

Regards

Jozsef

Tags (2)
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Qualify multiple txt files into one table

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

8 Replies
MVP & Luminary
MVP & Luminary

Re: Qualify multiple txt files into one table

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
New Contributor II

Re: Qualify multiple txt files into one table

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
Valued Contributor II

Re: Qualify multiple txt files into one table

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.

MVP & Luminary
MVP & Luminary

Re: Qualify multiple txt files into one table

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
New Contributor II

Re: Qualify multiple txt files into one table

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
New Contributor II

Re: Qualify multiple txt files into one table

Dear Marcus,

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

😞

BR

Jozsef

MVP & Luminary
MVP & Luminary

Re: Qualify multiple txt files into one table

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
New Contributor II

Re: Qualify multiple txt files into one table

Dear Marcus,

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

Best Regards

Jozsef