Cross Table

[Prabhu1204]

Hello Everyone,

Is it possible to do the cross table using the excel default field name,

I have a site in A1 and Team Name in A2.. The site name and team will be different for all the files. So I want to bring A1 and A2 as a column. 

TIA

[Taoufiq_Zarra]

@Prabhu1204  can you share a sample data and the expected output

[Prabhu1204]

The data will be like the attached. I have to use A and B field for implementing section access

[Taoufiq_Zarra]

@Prabhu1204  always 2 rows by file ?

[Prabhu1204]

Yes, I should map the two rows with my main data. Column A would be  Sites and Column B would Type.

[Taoufiq_Zarra · Accepted solution]

@Prabhu1204  if I understood correctly:

you have for example a folder with numbers (example 3) of Excel file (or other format) like :

and each file contain for exemple two line like :

and you want an output with Sites and Type for all file ?

so maye be this help, you can optimise the script if u want :

FOR Each File in filelist ('C:\Users\t.zarra\Downloads\Qlik24122020\File*.xlsx')
		
	DATA:
	LOAD A as Sites
	FROM
	'$(File)'
	(ooxml, no labels, table is Sheet1)  Where recno()=2;
	
	join
	 	
	LOAD A as Type
	FROM
	'$(File)'
	(ooxml, no labels, table is Sheet1)  Where recno()=3;


NEXT File;

SET vConcatenate = ;   

FOR i = NoOfTables()-1 to 0 STEP -1


  LET vTable=TableName($(i));
  
  Final:
  $(vConcatenate)
  LOAD distinct *,'' as TmpField RESIDENT [$(vTable)];

  DROP TABLE [$(vTable)];
 SET vConcatenate = Concatenate; 

NEXT i

drop fields TmpField;

output: