Join Tables with a For Each Loop

[brunolelli87]

Hello guys,

How can I merge, or join, a table with  For Each Loop?
Please, check my code!

The goal is to create a single table containing all fields from both tables!

For each Sticker in  'AZUL4', 'GOLL4', 'AMAR3'

DemonstrativoResultados:
LOAD 
	 '$(Sticker)' &  @1 as  [KKEY],		// Key para as tabelas
	 @1 				as	[PERI],		// Periodo
     @2					as	[RBVS], 	// Receita Bruta de Vendas e/ou Serviços.
     @3					as 	[DDRB], 	// Deduções da Receita Bruta.
     @22 				as 	[PECE],  	// Participações/Contribuições Estatutárias.
     @23 				as 	[RJCP],  	// Reversão dos Juros sobre Capital Próprio.
     @24 				as 	[PACD],  	// Part. de Acionistas Não Controladores DR.
     @25 				as 	[LOPP] 		// Lucro/Prejuízo do Período.
FROM
[http://lelli.000webhostapp.com/InvestmentsDR/$(Sticker).csv]
(txt, utf8, no labels, delimiter is ',', msq, filters(
Remove(Row, Pos(Top, 1)),
Transpose(),
Remove(Row, RowCnd(CellValue, 4, StrCnd(null))),
Remove(Row, Pos(Top, 1))
));


Join (DemonstrativoResultados)
LOAD 
     '$(Sticker)' & @1  as [KKEY],		// Key para as tabelas
     @2					as [ATTO], 		// Ativo Total
     @3 				as [ATVC], 		// Ativo Circulante 
     @53 				as [AAPA], 		// Ajustes de Avaliação Patrimonial
     @54 				as [AACO], 		// Ajustes Acumulados de Conversão 
     @55 				as [ORAB], 		// Outros Resultados Abrangentes
     @56 				as [AAC1] 		// Adiantamento para Futuro Aumento Capital1

FROM
[http://lelli.000webhostapp.com/InvestmentsCSV/$(Sticker).csv]
(txt, utf8, no labels, delimiter is ',', msq, filters(
Remove(Row, Pos(Top, 1)),
Transpose(),
Remove(Row, RowCnd(CellValue, 2, StrCnd(null))),
Remove(Row, Pos(Top, 1))
));


next

Drop Table DemonstrativoResultados;

Thanks

[lblumenfeld · Accepted solution]

I think what you're saying is that you want do do the join for each value in the loop and then combine the results. Is that correct? If so, then you'll want to concatenate the joined table at the end of each iteration of the loop.

First, let's set up an empty table containing the fields we want. Put this at the top, before the For loop.

ResultTable:
LOAD * Inline [
   [KKEY], [PERI], [RBVS], [DDRB], [PECE], [RJCP], [PACD], [LOPP], [ATTO],  [ATVC], [AAPA], [AACO], [ORAB], [AAC1]
];

  Then at the end of the loop, before the "Next", put this.

ResultTable:
Concatenate  (ResultTable) LOAD 
   [KKEY],
   [PERI],
   [RBVS],
   [DDRB],
   [PECE],
   [RJCP],
   [PACD],
   [LOPP],
   [ATTO],
   [ATVC],
   [AAPA],
   [AACO],
   [ORAB],
   [AAC1]
Resident DemonstrativoResultados;

After your loop finishes,  ResultTable should contain all the records.

Drop Table DemonstrativoResultados at the end.

I hope this helps.

[Saravanan_Desingh]

Yes, I agree with @lblumenfeld solution.

[brunolelli87]

Thank you so much!

It's working perfectly!

Would you mind to tell me why do we have to have the fields in ONE horizontal line at the beginning? And, in vertical aliment at the end?

And, is there any way to break the lines, so I can see all fields without rolling the page? Take a look at my line 4!

Thanks

Bruno

[lblumenfeld]

The fields for the inline load have to be on one (horizontal) line. In an inline load, the records are separated by each new line. The first line is the column names. In this case we don't have any data, just column names. We're essentially setting up the table with no records, just so it exists, so that we can "Concatenate Load" to it later.