Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
@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:
@Prabhu1204 can you share a sample data and the expected output
The data will be like the attached. I have to use A and B field for implementing section access
@Prabhu1204 always 2 rows by file ?
Yes, I should map the two rows with my main data. Column A would be Sites and Column B would Type.
@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: