topic Cross Table in App Development

Cross Table

Prabhu1204 — Wed, 22 Dec 2021 19:11:32 GMT

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

Re: Cross Table

Taoufiq_Zarra — Thu, 24 Dec 2020 09:13:23 GMT

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

Re: Cross Table

Prabhu1204 — Thu, 24 Dec 2020 09:45:23 GMT

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

Re: Cross Table

Prabhu1204 — Thu, 24 Dec 2020 09:46:05 GMT

Re: Cross Table

Taoufiq_Zarra — Thu, 24 Dec 2020 09:58:30 GMT

@Prabhu1204 always 2 rows by file ?

Re: Cross Table

Prabhu1204 — Thu, 24 Dec 2020 10:05:20 GMT

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

Re: Cross Table

Taoufiq_Zarra — Thu, 24 Dec 2020 13:33:27 GMT

@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: