topic Re: Concatenate Issue in QlikView

Concatenate Issue

tamilarasu — Mon, 15 Jun 2015 06:51:48 GMT

I am trying to concatenate multiple excel sheets from workbooks. So I am using loop to consolidate the data. But I am getting an error and I am not sure where I a wrong. Can any one check and correct the code?

SET Counter = 0;
FOR EACH file in FileList('C:\Users\Tamilarasu.Nagaraj\Desktop\RPC n\Sample\Sample - Copy\*.xlsx');


Let Counter = Counter +1;
ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];


Temp:
SQLtables; 
DISCONNECT;


FOR i = 0 to NoOfRows('Temp')-1
LET sheetName = purgeChar(peek('TABLE_NAME', i, 'Temp'), chr(36));
LET sheetName=replace(sheetName,chr(39)&chr(39),chr(39));


Table:
Load * ,
FileBaseName()as FIle,
FileDir() as Dir,
FileName() as File_Name,
'$(sheetName)' as Sheet_name


From $(file)(ooxml, embedded labels, table is [$(sheetName)], filters(
Remove(Row, RowCnd(CellValue, 1, StrCnd(null)))));


If (Counter =1 and i =0) then


NoConcatenate
Consolidated:
Load *
Resident Table;


Drop Table Table;


Else


Concatenate (Consolidated)
Rem:
Load * 
Resident Table; 


Drop Table Table;


End If


NEXT i
Drop table Temp;

Thank you.

Re: Concatenate Issue

pokassov — Mon, 15 Jun 2015 06:59:56 GMT

Hello!

Resident table is a table that was previous loaded in your script. I didn't see it.

You have to use something like this:

Load *;

sql select * from $(sheetName);

Re: Concatenate Issue

Gysbert_Wassenaar — Mon, 15 Jun 2015 07:05:24 GMT

This doesn't look right to me: Let CounterCounter = Counter +1;

Re: Concatenate Issue

tamilarasu — Mon, 15 Jun 2015 07:08:24 GMT

I am getting SQL error now.

First, I am loading the first sheet in table, then I am storing the data into consolidate table. After that I drop the Table Table (To avoid Joining), then the loop goes again and storing the data into table. Next, trying to concatenate the data with Consolidated Table (Where I am getting error).

Re: Concatenate Issue

tamilarasu — Mon, 15 Jun 2015 07:10:43 GMT

Yes, It should be Counter= Counter + 1. I have pasted the code in Syntax Highlighting -> XML, but it convert the above line wrongly as CounterCounter = Counter +1;

Re: Concatenate Issue

pokassov — Mon, 15 Jun 2015 07:12:13 GMT

O, I missed it.

Re: Concatenate Issue

pokassov — Mon, 15 Jun 2015 07:15:02 GMT

May be your first table has 0 rows? Can you add checking for NoOfRows('Consolidated') and use concatenate only if it has rows?

Re: Concatenate Issue

Gysbert_Wassenaar — Mon, 15 Jun 2015 07:20:02 GMT

Ok, if that CounterCounter is not the real problem then try adapting this code:

/*--------------------------------------------------------------------------------------------------*/
SUB LoadAllExcelSheets(vFile)


ODBC CONNECT TO [Excel Files;DBQ=$(vFile)];

tables:
SQLtables;
DISCONNECT;

LET vSheetCount = NoOfRows('tables');

FOR i = 0 to $(vSheetCount) -1


  // The replace function is used because the sheet names in my example files contain a period character.
  // The odbc driver replaces these with the # character. These # characters need to be turned in periods again.
  LET vSheetName = replace(purgeChar(purgeChar(peek('TABLE_NAME', $(i), 'tables'), chr(39)), chr(36)),'#','.');
   
    $(vConcatenate)
    [$(vSheetName)]:
    LOAD *, '$(vSheetName)' as Sheet, '$(vFile)' as FileName
    FROM [$(vFile)] (ooxml, embedded labels, table is [$(vSheetName)]);
  SET vConcatenate = Concatenate;
NEXT i

DROP TABLE tables;


END SUB


/*--------------------------------------------------------------------------------------------------*/
SUB ScanFolder(Root)
          FOR EACH FileExtension in 'xlsx'
                    FOR EACH FoundFile in filelist( Root & '\*.' & FileExtension)                 
                CALL LoadAllExcelSheets('$(FoundFile)');                   
                    NEXT FoundFile
          NEXT FileExtension
          FOR EACH SubDirectory in dirlist( Root & '\*' )
                    CALL ScanFolder(SubDirectory)
          NEXT SubDirectory
END SUB

SET vConcatenate= ;
CALL ScanFolder('d:\data\my xl files') ;

Re: Concatenate Issue

tamilarasu — Mon, 15 Jun 2015 07:27:22 GMT

Gysbert,

I am getting an error window. I have attached Sample excel data for your convenient.

Re: Concatenate Issue

tamilarasu — Mon, 15 Jun 2015 07:30:23 GMT

pokassov

I have checked the no of lines in debug mode and it has 131 rows.

Re: Concatenate Issue

Gysbert_Wassenaar — Mon, 15 Jun 2015 07:32:41 GMT

Either the ODBC driver is 32-bit and your Qlikview is 64-bit or the other way around. You need to make sure they're the same or you need to use the CONNECT32 keyword to force use of the 32-bit driver.

Re: Concatenate Issue

tamilarasu — Mon, 15 Jun 2015 07:49:16 GMT

Gysbert: Working fine . But I have two additional columns in 2nd workbook and I want to add them in the table. Any suggestion.?

Re: Concatenate Issue

Gysbert_Wassenaar — Mon, 15 Jun 2015 07:58:50 GMT

The number of fields does not need to be the same. Using the Concatenate keyword will force concatenation.

Re: Concatenate Issue

tamilarasu — Mon, 15 Jun 2015 08:06:03 GMT

Sorry, I could not get the exact result (Final output doesn't contains the below two fields). I have below two fields in Book3 (all three sheets) but the loop didn't take this fields. Could you advice.

Thanks for you help on this.

Re: Concatenate Issue

tamilarasu — Mon, 15 Jun 2015 08:31:37 GMT

Also, first excel data only loaded (i.e. only 4 rows but it should be 8 rows). Seems the second excel not concatenated into the table.

Re: Concatenate Issue

tamilarasu — Mon, 15 Jun 2015 08:42:13 GMT

Hi Gysbert:

I have found the problem and modified your code (Just two lines) and I got the desired output.

$(vConcatenate)

//[$(vSheetName)]: // I have comment this line

Consolidated:

LOAD *, '$(vSheetName)' as Sheet, '$(vFile)' as FileName

FROM [$(vFile)] (ooxml, embedded labels, table is [$(vSheetName)]);

SET vConcatenate = Concatenate (Consolidated);

NEXT i

Thanks a lot.

Re: Concatenate Issue

tamilarasu — Mon, 15 Jun 2015 08:50:30 GMT

I have one more doubt. Can you check the below attached file. Its weird and I don't know the reason. I guess the problem should be in excel format (!!). Please advice.