Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
An issue has been identified on Qlik Cloud hub, please visit our Status Update Page for details: GET THE LATEST
cancel
Showing results for 
Search instead for 
Did you mean: 
brunolelli87
Creator II
Creator II

Join Tables with a For Each Loop

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

1 Solution

Accepted Solutions
lblumenfeld
Luminary Alumni
Luminary Alumni

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.

View solution in original post

4 Replies
lblumenfeld
Luminary Alumni
Luminary Alumni

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
Creator II
Creator II
Author

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!

Result 4.png

 

Thanks

Bruno

lblumenfeld
Luminary Alumni
Luminary Alumni

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.