Left Join Multiple Files TXT

[edugallowk]

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

[edugallowk · Accepted solution]

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;

[hopkinsc]

Thats great news. 

Can you mark the appropriate response as correct and any others as helpful to close the ticket please?