Issue with leftjoining loop results

[Peony]

Hi there. I have a table which columns should fliped from horizontal to vertical view. 
I have this:

let_code	ENG	GER
head_1	English	Deutsch
head_2	Select your language	Bitte wählen Sie Ihre Sprache
head_3	Customer Feedback	Kundenfeedback

I need this:

let_code	head_1	head_2	head_3
ENG	English	Select your language	Customer Feedback
GER	Deutsch	Bitte wählen Sie Ihre Sprache	Kundenfeedback

So I create script that make this flip, but suddenly after I left join results I have data only for ENG, and GER has lost somewhere. Please help me to find mistake in code below:

Translate_TMP:
Load * Inline [
let_code,  	ENG,                   GER,                           
head_1, 	English,               Deutsch,                       
head_2,		Select your language,  Bitte wählen Sie Ihre Sprache,
head_3, 	Customer Feedback,     Kundenfeedback,               
];

Set vLanguage_Short_Name = 'ENG','GER';

Final: //"3_letters" as field name istead of "let_code" to avoid connection with Translate_TMP 
Load * Inline[
 "3_letters"
 ENG
 GER
];

For Each vLanguage_Short_Name in $(vLanguage_Short_Name)
Trace vLanguage_Short_Name $(vLanguage_Short_Name);

	For i = 1 to FieldValueCount('let_code')

		Let vField = FieldValue('let_code', $(i));
		Trace i $(i);
		
		Left Join (Final)
		Load Distinct
			'$(vLanguage_Short_Name)' as [3_letters],
			[$(vLanguage_Short_Name)] as [$(vField)]
		Resident
			Translate_TMP
		Where 
			[let_code] = '$(vField)'
		;

	Next i
	
Next vLanguage_Short_Name

Drop Table Translate_TMP;

And here is result I get:

I already despaired of finding the cause of the problem on my own.😭

[marcus_sommer]

Within the most scenarios are joins not applicable within loops. The reason are the added fields because in the first iteration the new fields are added but in the second iteration the new fields aren't longer new else they become now join-keys ... which is usually not wanted or expedient.

Quite more relevant is the question why do you want to create such crosstable? Personally I would rather tend to a solution like:

t: crosstable(Language, Value, 1) load * from table;

and then you could access each value you want per set analysis, like:

maxstring({< Language = p(Language), let_code = {'head_1'>} Value)

[vinieme12 · Accepted solution]

Use below

temp:
load
* inline [
let_code,ENG,GER
head_1,English,Deutsch
head_2,Select your language,Bitte wählen Sie Ihre Sprache
head_3,Customer Feedback,Kundenfeedback
];

let vNoOfCols = NoOfFields('temp');
let vNoOfRows = NoOfRows('temp');

LEt vColName='';
For vCol = 1 to $(vNoOfCols)
  let vColName = FieldName($(vCol), 'temp') ;
  If  NOT '$(vColName)'='let_code' Then
	trace $(vColName);
    
    Let vfieldList='';
    Let vfieldName='';
    For vRows = 0 to $(vNoOfRows) -1;
    
    Let vfieldName = peek('let_code',$(vRows),'temp');
    LEt vfieldList = '$(vfieldList),'&'Peek(''$(vColName)'', $(vRows), ''temp'') as [$(vfieldName)]';
	
    
    Next vRows
    Trace $(vfieldList);
    
	Trace iterno();
    
    IF vCol=2 Then  // Assuming Column 1 is always let_code
    Main:
      Load 
      '$(vColName)' as let_code
      $(vfieldList)
      autogenerate(1);

    ElseIF vCol>2
    Concatenate(Main)
      Load 
      '$(vColName)' as let_code
      $(vfieldList)
      autogenerate(1);
    End IF
    
  End If


next vCol
Drop table temp;

[vinieme12]

Alternate script using Left Joins

temp:
CrossTable(lang,val,1)
load
recno() as key
,* inline [
let_code,ENG,GER
head_1,English,Deutsch
head_2,Select your language,Bitte wählen Sie Ihre Sprache
head_3,Customer Feedback,Kundenfeedback
];

For each key in fieldvaluelist('key')
if $(key)=1 Then
  Main:
  Load lang,val as $(key)
  Resident temp
  Where key=$(key)
  and lang<>'let_code';

ELSE
  left join(Main)
  Load lang,val as $(key)
  Resident temp
  Where key=$(key)
  and lang<>'let_code';

End if
// trace 
next key

Map_Names:
Mapping Load
key,val
Resident temp
where lang='let_code';
drop table temp;

Rename fields using Map_Names;

exit Script;

[Peony]

Hey @vinieme12  Thank you for you help and ideas you shared. They help a lot!!!

[Peony]

Hi @marcus_sommer  thank you for an explanation of the issue I faced. Will use this knowledge now.