Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
edugallowk
Contributor III
Contributor III

Left Join Multiple Files TXT

Hello, I would like to request help from you.

I have several TXT files and would like to open a table by downloading all the columns and once they have been replicated in a new column where the key is the name of the file. I need to get the due date for each file.

If I do a left join, it works, but it will have two tables and I want to consolidate all the content into just one table. When I use only LOAD, it brings information only from the first file it finds.

Can someone help me?

[PRELOAD1]:
LOAD
FileBaseName() as PK_FileBaseName,
[A],
[B],
[C]
FROM [lib://AMIL/DM_*.txt]
(txt, codepage is 28591, embedded labels, delimiter is '#', msq, header is 5 lines)
;

/////////////////////////////////////////////////////////////////////////////////////////////

[PRELOAD2]:
LOAD DISTINCT 
FileBaseName() as PK_FileBaseName,
date_pay,
date_shipping
FROM [lib://AMIL/DM_*.txt]
(txt, codepage is 28591, no labels, comment is TESTE, delimiter is '#', no quotes, header is 2 lines, no eof)
;

I tried to use something described below in the PRELOAD2 table, but the result allowed the same.

vPath = 'lib://AMIL/';
For each File in FileList('$(vPath)/DM*.txt')

Left join 

[PRELOAD2]:
LOAD DISTINCT 
FileBaseName() as PK_FileBaseName,
date_pay,
date_shipping
FROM $(File)
(txt, codepage is 28591, no labels, comment is TESTE, delimiter is '#', no quotes, header is 2 lines, no eof)
Where WildMatch([@3],'Emissão*')
;

Next File;

 

Expected result:
the problem is just loading all records into the dates (date_pay | date_shipping) 
 
  PK_FileBaseName  | A | B | C | date_pay | date_shipping
1
2
3
4
5
Labels (4)
2 Solutions

Accepted Solutions
evan_kurowski
Specialist
Specialist

Hello edugallowk ,

A couple issues here:

1st issue:

Syntax of the load statements.  It looks like you're reading from the same series of files in both the first & second statement.  This implies a blending of record sets of the two load statements together into 1 file series (this would be unusual, but possible, with logic in the load script "untangling" the two subsets).

Another problem stems from the format of the second load statement attempting to load by named fields, in conjunction with the 'no labels' keyword.

In a "no labels" setting, your load statement would look more like this:

LOAD @1 AS PK_FileBaseName,
@2 AS date_pay,
@3 AS date_shipping
FROM
[DM_*.txt]
(
txt, codepage is 1252, no labels, delimiter is '#', msq, header is 5 lines);


 

2nd Issue - sequencing

When you iterate through the second load statement, via 'For each File in FileList('$(vPath)/DM*.txt')'

The first file found is performing a Left Join on a single key field 'PK_FileBaseName'.

On the second pass, both tables now contain the fields  PK_FileBaseName, date_pay, date_shipping and  join mechanics have converted to a 3 field compound key.

You need to concatenate all the collected files from the second table, before finally perform the Left Join a single time.


[PRELOAD1]:
LOAD
FileBaseName() as PK_FileBaseName,
[A],
[B],
[C]
FROM [DM_*.txt]
(
txt, codepage is 28591, embedded labels, delimiter is '#', msq, header is 5 lines)
;


[PRELOAD2]:
LOAD DISTINCT
FileBaseName() as PK_FileBaseName,
date_pay,
date_shipping
FROM [DM_*.txt]
(
txt, codepage is 28591, embedded labels, delimiter is '#', msq, header is 5 lines)
//(txt, codepage is 28591, no labels, comment is TESTE, delimiter is '#', no quotes, header is 2 lines, no eof)
;


LEFT JOIN(PRELOAD1)
LOAD * RESIDENT PRELOAD2;

DROP TABLE PRELOAD2;

contents of Data file DM_01.txt :
I
yam
a
5 line
header
A#B#C#date_pay#date_shipping
Ayyyy#Beeee#Ceeee#Friday#$5

contents of Data file DM_02.txt:

I
yam
a
5 line
header
A#B#C#date_pay#date_shipping
Deee#Eeee#eFFFF#Tuesday#$4

 

 

View solution in original post

edugallowk
Contributor III
Contributor III
Author

Hello! I want to thank you for your help.

It worked. Good job.

@evan_kurowski / @hopkinsc

PreLoad1:
Load
	[Código],
	[Beneficiário],
	[Matrícula],
	[CPF],
	[Plano],
	[Tipo],
	[Idade],
	if(IsNull([Dependência]) or [Dependência]='','Titular',[Dependência]) as [Dependência],
	Date([Data Limite] ) AS [Data Limite],
	[Data Inclusão],
	Date([Data Exclusão] ) AS [Data Exclusão],
	[Lotacao],
	[Rubrica],
    if(WildMatch(Rubrica,'Mens.*'),'1',
		if(WildMatch(Rubrica,'Co-participação*'),'2',
        if(WildMatch(Rubrica,'Cobrança*'),'3',
    	if(WildMatch(Rubrica,'Devolução*'),'4','Outros')))
    ) as [Rubrica_Custom],
	[Co-Participacao],
	[Outros],
	[Mensalidade],
	[Total Família],   
    
    /*Informações Adicionais*/
    SubField(FileDir(),'/',3) as Operadora_Grupo,
    SubField(FileBaseName(),'_',2) &REPEAT('0', 15 - LEN(SubField(FileBaseName(),'_',2))) as Subfatura_Custom,
    Date(Date#('01' &'/' &SubField(FileBaseName(),'_',4) &'/' &SubField(FileBaseName(),'_',3),'DD/MM/YYYY')) as Competencia_Vencimento,
    
    /*Informações do Arquivo*/
    FileDir() as FileDir,
    FileExtension() as FileExtension,
    FileName() as FileName,
    FilePath() as FilePath,
    FileSize() as FileSize,
    FileTime() as FileTime,
    FileBaseName() as PK_FileBaseName
FROM [lib://AMIL/DM_*.txt]
//FROM $(File)
(txt, codepage is 28591, embedded labels, delimiter is '#', msq, header is 5 lines)
Where WildMatch([Rubrica],'Mens.*');


PreLoad2:
Load Distinct
	FileBaseName() as PK_FileBaseName,
	date(SubField([@3],' ',-1)) AS EMISSAO,
	date(SubField([@4],' ',-1)) AS VENCIMENTO
FROM [lib://AMIL/DM_*.txt]
(txt, codepage is 28591, no labels, comment is TESTE, delimiter is '#', no quotes, header is 2 lines, no eof)
Where WildMatch([@3],'Emissão*')
;

Left join (PreLoad1)
Load * Resident PreLoad2;

Drop Table PreLoad2;

View solution in original post

21 Replies
hopkinsc
Partner - Specialist III
Partner - Specialist III

Hi

Cant you do

[PRELOAD1]:
LOAD
FileBaseName() as PK_FileBaseName,
[A],
[B],
[C]
FROM [lib://AMIL/DM_*.txt]
(txt, codepage is 28591, embedded labels, delimiter is '#', msq, header is 5 lines)
;

 

Left Join([PRELOAD1])
LOAD DISTINCT
FileBaseName() as PK_FileBaseName,
date_pay,
date_shipping
FROM [lib://AMIL/DM_*.txt]
(txt, codepage is 28591, no labels, comment is TESTE, delimiter is '#', no quotes, header is 2 lines, no eof)
;

 

edugallowk
Contributor III
Contributor III
Author

Screenshot_1.png

Using left, the result is conformed to the image, that is, it fills only the data of a file, leaving the others without the information. Do you have any idea how I could handle this?

Thank you!
hopkinsc
Partner - Specialist III
Partner - Specialist III

You have a where clause on the second table load. is that correct? 

that will only take the data where @3 contains Emissão.

So in your screenshot, the first row is DM_453653000_2018_09, does the @3 column in that file contain Emissão?

Are you able to post any examples?

edugallowk
Contributor III
Contributor III
Author

Yes! I put a where clause in an attempt to group only the data I need.

See in the drawing that there were only 11 different records.

 

Screenshot_2.png

edugallowk
Contributor III
Contributor III
Author

If using Left works, but I need to leave the result in a single table.

hopkinsc
Partner - Specialist III
Partner - Specialist III

Hi,

Sorry, quick question.

In your second load you are loading 

date_pay,
date_shipping

I can't find these fields in the files you attached. 

hopkinsc
Partner - Specialist III
Partner - Specialist III

actually im guessing the fields are Emissao and vencimento?

Not sure why you have the english names in the script?

hopkinsc
Partner - Specialist III
Partner - Specialist III

Hi,

try this. You will have to change the FROM clause and maybe the @1, @2 etc as i have done this in QlikView, but it would be the same as Qlik Sense. 

DatePayMap:
Mapping
LOAD
FileBaseName() as PK_FileBaseName,
subfield(@3,': ',2) as DatePay
FROM
[DM_*.txt]
(txt, codepage is 28591, no labels, comment is TESTE, delimiter is '#', no quotes, header is 2 lines, no eof)
Where WildMatch([@3],'Emissão*') ;

DateShippingMap:
Mapping
LOAD
FileBaseName() as PK_FileBaseName,
subfield(@4,': ',2) as DateShip
FROM
[DM_*.txt]
(txt, codepage is 28591, no labels, comment is TESTE, delimiter is '#', no quotes, header is 2 lines, no eof)
Where WildMatch([@3],'Emissão*') ;


PRELOAD1:
LOAD @1 as A,
@2 as B,
@3 as C,
FileBaseName() as PK_FileBaseName,
applymap('DatePayMap',FileBaseName(),null()) as Date_Pay,
applymap('DateShippingMap',FileBaseName(),null()) as Date_Shipping
FROM
[DM_*.txt]
(txt, codepage is 28591, no labels, delimiter is '#', header is 6 lines);

 

This gives the following result. 

Capture.JPG

 

evan_kurowski
Specialist
Specialist

Hello edugallowk ,

A couple issues here:

1st issue:
Syntax of the load statements.  It looks like you're reading from the same series of files in both the first statement & the second statement.  This implies you've blended the record sets of the two load statements together into 1 file series (this would be unusual, but possible, with logic in the load script "untangle" the two subsets).

Another problem stems from the format of the second load statement attempting to load by named fields, in conjunction with the 'no labels' keyword.

In a "no labels" setting, your load statement would look more like this:

LOAD @1,
@2,
@3
FROM
[DM_*.txt]
(
txt, codepage is 1252, no labels, delimiter is '#', msq, header is 5 lines);

 2nd Issue - sequencing

When you iterate through the second load statement, via 'For each File in FileList('$(vPath)/DM*.txt')'

The first pass is performing a Left Join on a single key field 'PK_FileBaseName'.

On the second loop pass, both tables contain the fields  PK_FileBaseName, date_pay, date_shipping and join mechanics have converted to a 3 field compound key.

You need to concatenate all the collected files from the second load statement, then perform the Left Join a single time at the end


[PRELOAD1]:
LOAD
FileBaseName() as PK_FileBaseName,
[A],
[B],
[C]
FROM [DM_*.txt]
(
txt, codepage is 28591, embedded labels, delimiter is '#', msq, header is 5 lines)
;


[PRELOAD2]:
LOAD DISTINCT
FileBaseName() as PK_FileBaseName,
date_pay,
date_shipping
FROM [DM_*.txt]
(
txt, codepage is 28591, embedded labels, delimiter is '#', msq, header is 5 lines)
//(txt, codepage is 28591, no labels, comment is TESTE, delimiter is '#', no quotes, header is 2 lines, no eof)
;



LEFT JOIN(PRELOAD1)
LOAD * RESIDENT PRELOAD2;

DROP TABLE PRELOAD2;



Data files contents DM_01.txt:

I
yam
a
5 line
header
A#B#C#date_pay#date_shipping
Ayyyy#Beeee#Ceeee#Friday#$5
 
 Data files contents DM_02.txt:

I
yam
a
5 line
header
A#B#C#date_pay#date_shipping
Deee#Eeee#eFFFF#Tuesday#$4