topic Re: Qliview - Query - Consolidation of binary files in App Development

Qliview - Query - Consolidation of binary files

leoamul_raj — Sun, 30 May 2021 12:00:57 GMT

Dear all,

please let me know why i am unable to get the output.

I need to check values when the loop i and j runs but the code runs successfully without any output.

table 'Filedata' is not created.

LET i =0;

LET j=0;

Uname:
LOAD Unit_Name
FROM
[E:\Binary_Test\Unit.xlsx]
(ooxml, embedded labels, table is Sheet1);

//Loop Through Files in FOlder

FOR EACH file in FileList('E:\Binary_Test\*.xlsb');

ODBC CONNECT64 TO [Excel Files;DBQ=$(file)];

xlsbtables:
SQLtables;

DISCONNECT;

FOR i = 0 to NoOfRows('xlsbtables')-1
FOR j = 0 to NoOfRows('Uname')-1


          Filedata:
          Load
          PURGECHAR(PURGECHAR(PEEK('TABLE_NAME', i, 'xlsbtables'), CHR(39)), CHR(36)) as sname,
          PEEK('Unit_Name', j, 'Uname') as uname
          ;
NEXT j;
NEXT i;
Next;

Re: Qliview - Query - Consolidation of binary files

JuanGerardo — Sun, 30 May 2021 12:27:46 GMT

Hi @leoamul_raj, your Load statement for Filedata needs an origin of rows to load (like the FROM clause in SQL). From where are you trying to load the sname and uname fields?

In case the two values are constants, you can use the following syntax:

Filedata: Load PURGECHAR(PURGECHAR(PEEK('TABLE_NAME', i, 'xlsbtables'), CHR(39)), CHR(36)) as sname, PEEK('Unit_Name', j, 'Uname') as uname Autogenerate(1) ;

Re: Qliview - Query - Consolidation of binary files

leoamul_raj — Sun, 30 May 2021 12:47:13 GMT

Thank you for the reply.

I need to consolidate the xlsb files based on the another file.

I have attached the files to consolidate(concatenate) 'alain.xlsb' and 'ajman.xlsb' based on the sheet names in 'unit.xlsx'

thanks,

leo

Re: Qliview - Query - Consolidation of binary files

leoamul_raj — Mon, 07 Jun 2021 11:23:06 GMT

Dear Team,

I did not get any reply.

Regards,

Leo