Excel sourced application auto update

[nareshthavidishetty]

Hi,

We were using excel as data source to the application.we were having,

No.of excel : 4

No.of sheets in each excel : 70 sheets(with non-identical sheet names)

Issue: It is not automatic like when ever an new sheet inserted in the common folder excel file the job got failed and we have checked with the application and the cause is due to huge number of synthetic keys generated the application is not responsive.

To avoid synthetic keys,when ever an new sheet added to any of the excel file we created an duplicate sheet in all excel files and this way we eliminate the synthetic keys.

Current challenge:Bring the application to automatic means with out manually interaction when ever the data got updated in common folder it should be fetch the data from the data source irrespective of sheet names 

Current logic:

LET vFilePath = 'F:\Qlikview_Dev\Qlikview_Template\External_Data\Budget';

Data:

LOAD

'' AS Dummy

AutoGenerate (0);

FOR EACH file in FileList('$(vFilePath)\*.xlsx');

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

SheetNames:

SQLtables;

DISCONNECT;

FOR index = 0 to NoOfRows('SheetNames')-1

LET sheetNamez = PurgeChar(PurgeChar(Peek('TABLE_NAME', index, 'SheetNames'), Chr(39)), Chr(36));

Concatenate(Data)

Tab:

Load *,

  Month( GltPostingDate) as GltMonth,

    Year( GltPostingDate) as GltYear,

  GltAcctUnit&'-'&GltAccount&'-'&GltSubAccount as SheetName,

  '$(sheetNamez)' as Sheet_names

From $(file)(ooxml, embedded labels, table is [$(sheetNamez)]);

NEXT index

DROP TABLE SheetNames;

NEXT

DROP FIELD Dummy;

Thanks..

[nareshthavidishetty]

Hi,

We were using excel as data source to the application.we were having,

No.of excel : 4

No.of sheets in each excel : 70 sheets(with non-identical sheet names)

Issue: It is not automatic like when ever an new sheet inserted in the common folder excel file the job got failed and we have checked with the application and the cause is due to huge number of synthetic keys generated the application is not responsive.

To avoid synthetic keys,when ever an new sheet added to any of the excel file we created an duplicate sheet in all excel files and this way we eliminate the synthetic keys.

Current challenge:Bring the application to automatic means with out manually interaction when ever the data got updated in common folder it should be fetch the data from the data source if it having different sheet names.

Current logic:

LET vFilePath = 'F:\Qlikview_Dev\Qlikview_Template\External_Data\Budget';

Data:

LOAD

'' AS Dummy

AutoGenerate (0);

FOR EACH file in FileList('$(vFilePath)\*.xlsx');

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

SheetNames:

SQLtables;

DISCONNECT;

FOR index = 0 to NoOfRows('SheetNames')-1

LET sheetNamez = PurgeChar(PurgeChar(Peek('TABLE_NAME', index, 'SheetNames'), Chr(39)), Chr(36));

Concatenate(Data)

Tab:

Load *,

  Month( GltPostingDate) as GltMonth,

    Year( GltPostingDate) as GltYear,

  GltAcctUnit&'-'&GltAccount&'-'&GltSubAccount as SheetName,

  '$(sheetNamez)' as Sheet_names

From $(file)(ooxml, embedded labels, table is [$(sheetNamez)]);

NEXT index

DROP TABLE SheetNames;

NEXT

DROP FIELD Dummy;

Thanks..

[Gysbert_Wassenaar]

I don't see the problem tbh. You create only one table and add everything to that table. The code you posted doesn't care about excel files having different sheet names. Can you give more specific information?

[Kushal_Chawda]

check this

Dynamically Loading Multiple Excel Files

[nareshthavidishetty]

Hi,

But the application is not responsive if we didn't maintain common sheet names in all excel files.

Thanks..

[Gysbert_Wassenaar]

Post a small example that demonstrates the problem. See this document for how to do that: Preparing examples for Upload - Reduction and Data Scrambling

[nareshthavidishetty]

Hi,

Please find the above attached sample data source and application.

Thanks..

[Gysbert_Wassenaar]

I don't see any problems with the files you posted other then that the 2014 file is missing the GltPostingDate on the GL Codes tab. If I remove the sheets with no data then the Qlikview document still reloads without problems. Your synthetic keys have nothing to do with the excel files and sheets you posted.