topic Re: Excel sourced application auto update in QlikView

Excel sourced application auto update

nareshthavidishetty — Wed, 25 Nov 2020 16:16:04 GMT

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..

Re: Excel sourced application auto update

nareshthavidishetty — Fri, 15 Apr 2016 09:29:05 GMT

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)

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 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..

Re: Excel sourced application auto update

Gysbert_Wassenaar — Fri, 15 Apr 2016 09:37:21 GMT

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?

Re: Excel sourced application auto update

Kushal_Chawda — Fri, 15 Apr 2016 09:47:31 GMT

check this

Dynamically Loading Multiple Excel Files

Re: Excel sourced application auto update

nareshthavidishetty — Fri, 15 Apr 2016 09:50:54 GMT

Hi,

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

Thanks..

Re: Excel sourced application auto update

Gysbert_Wassenaar — Fri, 15 Apr 2016 09:54:31 GMT

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

Re: Excel sourced application auto update

nareshthavidishetty — Fri, 15 Apr 2016 10:17:23 GMT

Hi,

Please find the above attached sample data source and application.

Thanks..

Re: Excel sourced application auto update

Gysbert_Wassenaar — Fri, 15 Apr 2016 10:31:11 GMT

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.