Hi all,

the product JDBC Connector reached end-of-life because it's technically outdated. Support of it ends 2018-12-31.

The successor product is the TIQ Java Service Connector and can be obtained here:

https://www.tiq-solutions.de/en/tiq-java-service-connector/

Best regards,

Ralf

--------------------------------

Hi everyone,

this is a demo version of our JDBC Connector for QlikView. It will enable a lot of non-ODBC/OLEDB sources for loading data into QlikView.

Also, it would give much more performance on QV 9 loads (Oracle, mysql etc.) and could speed up on complex network environments (like VPN, firewalls, WAN).

Please try it out and give us some feedback!

Installation instructions:

• install the JDBC Connector and config the JDBC driver (mostly a .jar file)
• connect with JDBC URL (e.g. jdbc:oracle:thin:@localhost:1521:ex)
• example connect statement for Oracle (will be included by connect popup):
  CUSTOM CONNECT TO "Provider=JDBCConnector_x64.dll;jdbc:oracle:thin:@localhost:1521:ex;XUserId=SbKdMaFNRLOA;XPassword=aWELJaFNRbTA;";

Missing feature (waiting for QV support):

• no select option for database/owner on table wizzard

Thanks to my colleague Sven "Benno" Uhlig who brought up this idea and implementation!

Ralf

UPDATE:

This is a new version which supports DDL and DML statements too. You will get a result set if a DML statement was executed with the update count.

Just try script calls like this:

DATA:

SQL CREATE TABLE test2 (col1 INT)

;

SQL insert into test2 (col1) values (1)

;

SQL update test2 set col1=2

;

SQL drop table test2

;

UPDATE:

New version comes with an installer and config dialogs. See also: http://www.tiq-solutions.de/display/enghome/ENJDBC for a list of possible JDBC sources.

Hi All,

Please find the Fiscal Year - Master Calender script and application.

Set vFM = 4 ;
LET vDateMin=num(makedate(2016,01,01));
LET vDateMax=floor(monthend(today()));
LET vDateToday = num(today());

CalenderTemp:

LOAD Distinct Date($(vDateMin) + RowNo() - 1,'DD-MMM-YYYY') AS Date,
month(Date($(vDateMin) + RowNo() - 1,'DD-MMM-YYYY')) AS Month,
year(Date($(vDateMin) + RowNo() - 1,'DD-MMM-YYYY')) AS Year,
monthname(Date($(vDateMin) + RowNo() - 1,'DD-MMM-YYYY')) AS Monthname,
Week(Date($(vDateMin) + RowNo() - 1,'DD-MMM-YYYY')) AS Week
AutoGenerate 1 While $(vDateMin) + IterNo() - 1 <=$(vDateMax);

Calender:
Load *,Dual(fYear-1 &'/'& fYear, fYear) as FYear,  // Dual fiscal year
   Dual(Month, fMonth)  as FMonth   // Dual fiscal month
  ;
load *,
Year + If(Month>=$(vFM), 1, 0) as fYear,  // Numeric fiscal year
   Mod(Month-$(vFM), 12)+1  as fMonth   // Numeric fiscal month
Resident CalenderTemp;
drop table CalenderTemp;

          How To / Missing Manual

Attached is an example of stock aging in SAP. In this example I have used the MSEG (Document Segment) and MKPF (Material Document Header) tables to identify both the current stock position and the purchases (the In movements of stock). The final stock value can then be distributed across the most recent stock in movement records to assign a date as to when this stock was received (based on a FIFO - first in first out methodology). The MKPF table is required as there was an incomplete set of records in the BUDAT (stock-in date) field in the MSEG table in this implementation (this may be different at other sites).

The basic process is as follows:

1. identify the purchase instances (headers) from the MKPF table. This is required to get the stock-in date for each purchase instance (BUDAT).
2. Join in the purchase records (where SHKZG = 'S' to identify in movements of stock) and the unit cost of these movements by material (MATNR) and location (LGORT). The MSEG and MKPF tables join based on the MANDT, MBLNR and MJAHR fields. I have chosen to right join the MSEG table as I only require the purchase records (i.e. SHKZG = 'S').
3. Calculate the latest final stock values for each Material (MATNR) and Location (LGORT) and add this against each purchase line. This does repeat the final stock value across many lines but that is used in the next step.
4. Calculate the distribution of the current stock over the most recent preceding purchases. I have also calculated the cost of the stock based on the unit value of the final stock as calculated in step 3.

As this involves a bit of logic to look at preceeding values etc I have attached an example which shows the 4 steps in the coding that you can copy and reuse. The example is based on the QVDs as they would be generated using the SAP Connector script generator** so if you have used this it should be an easy reload of this data.

**Note: the MATNR field is renamed to make it a key in MSEG as [%MATNR_KEY].

Any clarifications or improvements please feel free to add in comments below.

Below script helps in loading all the tables in the database and store that tables in QVDs.

ODBC CONNECT TO MyODBC;

SQLTableList:

LOAD "TABLE_NAME" as  TableNames;

SQL SELECT DISTINCT "TABLE_NAME"

FROM "INFORMATION_SCHEMA".COLUMNS;

Note:

Information schema.columns is for MS SQL Server database, you can replace that with the euqivalent table in the corresponding database. 

For oracle ALL_TABLES   ALL_TABLES

For MYSQL - all_tables or SHOW_TABLES

How to List All Tables and Describe Tables in Oracle, MySQL, DB2 and PostgreSQL | OneWebSQL

MySQL :: MySQL 5.7 Reference Manual :: 13.7.5.37 SHOW TABLES Syntax

Let vTableCount = NoOfRows('SQLTableList');

Let vTableCountQVD = 'dbo.[' & NoOfRows('SQLTableList') & ']';

For i = 0 To $(vTableCount) -1

LET vMyTableName = 'dbo.[' & Peek('TableNames', $(i), 'SQLTableList') & ']';

LET vMyTableNameQVD = Replace(Peek('TableNames', $(i), 'SQLTableList'), ' ', '');

$(vMyTableNameQVD):

SQL SELECT *FROM $(vMyTableName);

STORE $(vMyTableNameQVD) INTO $(ExtractQVD)$(vMyTableNameQVD).QVD;

DROP Table $(vMyTableNameQVD);

Next i

Hope this helps others.

Regards,

Jagan.

Hello Everyone,

I have attached the document for the important Qlikview functions used in script as well as in UI.

Please have a look and also feel free to update the document or comment in the session for the functions which is missed.

Generic Keys is a way to define keys between tables in a more general way so that their values can represent other things than individual key values; they can represent groups of key values or any key value. As an example, you can combine product IDs, product group IDs and a symbol for all products into one key field.

You can use generic keys to solve many data modeling problems:

• Authorization table with OR-logic between fields
  If you have an authorization table you sometimes want to have a slightly more complex access restriction than a simple logical AND between fields. It could be e.g., that a user is allowed to see sales for all regions for a specific product and at the same time the European sales for all products. Generic keys can be used here.
• Mixed dimensional granularity in a single fact table
  Often you want to compare actual numbers with budget numbers. The standard method is to concatenate these two tables into one common fact table. However, this new fact table could have mixed granularity in many of the dimensions. Generic keys can be used here.
• Multiple fact tables linked using a master link table
  Sometimes you have fact tables that are so different that you don’t want to concatenate them. To solve this problem you can make a data model that has a central link table and uses generic keys.

See more in the attached files.

HIC

PS I have been asked to make an example on comparing budget and actual numbers, so here it comes as a second attachment. It is a zip file with a mock-up order database with a budget. Create a new empty qvw; expand the zipped files in the same folder as the qvw; insert one of the two included script files into the qvw and run the script.

Ejemplos de Función Date# y Date para transformar fechas y luego darles formato. Esto como método alternativo a MakeDate, que usa 4 funciones haciendo más lento el load:

MakeDate(Left(20130628,4), Mid(20130628,5,2),Right(20130628,2))

1. Date#(20130628,'YYYYMMDD')

Devuelve 41453 en formato 20130628

2. Date(Date#(20130628,'YYYYMMDD'),'DD-MMM-YY')

             Devuelve 41453 con formato 28-jun-13

3. Date(Date#('2013.06.28','YYYY.MM.DD'),'DD-MMM-YY')

             Devuelve 41453 con formato 28-jun-13

4. Date(Date#('06-28-13','MM-DD-YY'),'DD-MMM-YY')

             Devuelve 41453 con formato 28-jun-13

5. Date(Date#('28.06.13' ,'DD.MM.YY'),'DD-MMM-YY')

              Devuelve 41453 con formato 28-jun-13

6. Date(Date#('jun.28.2011', 'DD.MMM.YYYY'), 'DD-MMM-YY')

              Devuelve 41453 con formato 28-jun-13

7. Para eliminar hora de una fecha

Date(Floor( '2013-06-28 07:30:38' ),'DD-MMM-YY')

                     Devuelve 41453 con formato 28-jun-13 sin minutos

8. Para obtener solo la hora de una fecha

Time(Frac( '2013-06-28 11:30:38' ),'hh:mm:ss')

     Devuelve 0,47960648148 con formato 11:30:38

9. Para obtener Fecha y Hora

             Date( Timestamp#( '20130628 11:30:38', 'YYYYMMDD hh:mm:ss'), 'DD-MMM-YY hh:mm:ss'   )

                      Devuelve 41453,479606481  con formato 28-jun-13 11:30:38

10. Para obtener día de la semana en fecha

             Date( '2013-06-28', 'WWW DD-MMM-YY')

                      Devuelve 41453  con formato  vie 28-jun-13

11. Resta un mes a la fecha

AddMonths('2013-06-28',-1)

Devuelve 41422 en formato 28/05/2013 (formato predeterminado)

12. Suma un mes a la fecha

AddMonths('2013-06-28', 1)

Devuelve 41453 en formato 28/07/2013 (formato predeterminado)

Esta nota esta publicada en el blog: Qlikview en Español

NOTAS:

Al indicar un formato de hora en Qlikview la letra mayuscula M= mes,  y letra minuscula m= minutos

Referencias (ingles)

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/19/why-don-t-my-dates-work

http://community.qlik.com/docs/DOC-3102

One of our Qlik Community Buddy (Rupinder Singh) shared an awesome way to optimize the performance of group by statement but I don't think this got enough lime light which it should have. In order to give it better exposure, I am creating this document from this thread Optimize Group By

For below I am using QV version - 11.2 SR4

I have always had trouble with aggregating my data as "Group by" statements in scripts have always taken too long for my comfort.

For example, to group by  23 million records (2 columns - ID and Sales) it takes me 10 mins.

Table_Group:

Load ID,Sum(Sales)

Resident Table

Group By ID;

Instead of this, If I Order by my data by ID and then run the same Group by clause, I get results backs in less that 2 mins.

Table_Sort:

Load ID,Sales

Resident Table

Order by ID;

Table _Group:

NoConcatenate Load ID,Sum(Sales)

Resident Table_Sort

Group By Table_Sort;

I would have believed QV to internally perform this step, but explicitly implementing this has improved my query times by 500%.

I have tables with 40 columns (36 of which to be grouped by and 4 to be summed) and the query time has gone from 18 to 6 mins.

I have just ensured that my Order By column list is the same as Group By Column List.

So, If Order by is "Order By Column1,Column2,Column3" then Group by is "Group By Column1,Column2,Column3"

I would love to hear back if others in the community also have faced similar challenges and whether this or any other method has helped them improve Group by Performance.

Hi All,

The script below loads the data into qlikview file for multiple sheets in a single load by using the Loop statements. 

LET vStartSheetNumber = 1;

LET vEndSheetNumber = 50;

LET vExcelFileName = 'Data';

// Generate Empty table

Data:

LOAD

          '' AS Data.Field1,

  '' AS Data.Field2

AutoGenerate(0);

FOR index = vStartSheetNumber TO vEndSheetNumber

     Concatenate(Data)

     LOAD

          *

     FROM [$(vExcelFileName).xlsx]

     (ooxml, embedded labels, header is 10 lines, table is [Page $(index)]);

NEXT

Note : in my excel file the sheet names are in the patter of Page 1, Page 2, ..... Page n.

Hope this helps others.

Regards,

Jagan.

Having in the past used the ODBC method of getting .xlsx sheetnames (How to load EXCEL worksheets and retrieve tab names), I recently worked for a client who didn't have access to be able to install the ODBC driver required on the server.

I was eventually able to get on OLEDB connection working, this shouldn't require an installation either on the client or server side.

The code below will read data from the excel file specified in the vFilePath variable.

It then reloads the data in the tables2 resident load and excludes the sheet names we do not to want to load.

Finally we use a For Each Loop to loop through the sheets and load them into Qlikview.

Hope this helps someone!

Let vFilePath = '\\path\to\my\file\';

//Get the Sheet Name of the file

FOR EACH file in FileList('$(vFilePath)');

//In order to get the file information from SQLtables command making use of the OLEDB connection

OLEDB CONNECT32 TO  [Provider=Microsoft.ACE.OLEDB.12.0;Data Source='$(vFilePath)';Extended Properties="Excel 12.0;HDR=YES";];

tables:

SQLtables;

DISCONNECT;

FOR i = 0 to NoOfRows('tables')-1

LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));

Concatenate

Table:

Load

FileBaseName()as FIle,

FileDir() as Dir,

FileName() as File_Name,

'[' & '$(sheetName)' & ']' as Sheet_name

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

NEXT i;

Next

NoConcatenate

//Drop sheetnames we dont need/cant use

tables2:

Load Sheet_name Resident tables

Where not MixMatch(Sheet_name, '[Sheet1]'); //<< Incomplete Sheets

Drop Table tables;

//Loop through the sheetnames in the file and load our data

FOR EACH Sheet in FieldValueList('Sheet_name')

MyFile:

LOAD  field1,

  field2,

  field3,

     '$(Sheet)' as Sheet,

     Year(Date(Filetime())) as filetime,

     right(FileDir(),4) as Year

FROM

[$(vFilePath)]

(ooxml, embedded labels, header is 2 lines, table is  $(Sheet));

next;

Drop Table tables2;

Its a tedious task to generate Qvd's while developing a Qv application.

Here's a Qvd Generator to ease the life of Qv Developer.

Just edit the script, making changes to the OLE DB / ODBC connection, & schema name.

Change the path where the Qvds need to be saved, exit the script.

Then just click the "Generate Qvd's" text in white.

The Qvd's will be generated at the mentioned path.

Hope this will ease someones efforts. !

Thanks & Regards

Swapneel

Find the attachement to implement google maaps in qlikview. Documents with screenshots are avail. Attached the QVW file also for your reference. Regards. Siva Sankar k

I've re-edited the document  and fixed the  issue. Regards, siva sankar

The  attached file SectionAccess.zip contains several example documents that show how Section Access can be used to restrict access to specific users and to specific data.

This is not a guide on how to use Section Access, but instead shows some of the common errors and how to avoid them.

An excellent guide to Section Access can be found here http://community.qlik.com/docs/DOC-1853

Marcus_Sommer has accumulated a comprehensive list of Section Access Resources in this document Section Access

These examples will require a licenced copy of QlikView to open them, so they not suitable for users with QlikView Personal Edition.

The example documents will ask for a username and password to open them and set the Section Access permissions. The following usernames and passwords can be used to open the documents and see the different functionality with Section Access. When changing from one user to another, you must close QlikView before you can open a document as another user.

UserID,  Password

ADMIN,  ADMIN

JOHN, JOHN

MARY,  MARY

PETER,  PETER

SIMON,  SIMON

You can view the load script to see how the Section Access and data is configured.

Section Access Example 1

This document attempt to restrict access to a list of named users, but this fails as the section access field names are not set as UPPERCASE in the script.

Section Access Example 2

The app is now correct and prompts for a username and password before users can access the data.

Examples 1 & 2 use the Basic section access data from SectionAccess.xlsx

Access,  UserID,  Password

USER,  USER,  USER

ADMIN,  ADMIN,  ADMIN

USER,  JOHN,  JOHN

USER,  MARY,  MARY

USER,  PETER,  PETER

ADMIN,  SIMON,  SIMON

Section Access Example 3

The data is restricted so users can only access the Cost Centre data as listed in the Cost Centre  column in Section Access.

Access,  UserID,  Password,  CostCentre

USER,  USER,  USER,  *

ADMIN,  ADMIN,  ADMIN,  *

USER,  JOHN,  JOHN,  A

USER,  MARY,  MARY,  B

USER,  PETER,  PETER,  C

ADMIN,  SIMON,  SIMON,  A

Section Access Example 4

This adds extra DUMMY rows to the section access table so the * entries can now access all cost centres.

Access,  UserID,  Password,  CostCentre

USER,  USER,  USER,  *

ADMIN,  ADMIN,  ADMIN,  *

USER,  JOHN,  JOHN,  A

USER,  MARY,  MARY,  B

USER,  PETER,  PETER,  C

ADMIN,  SIMON,  SIMON,  A

DUMMY,  DUMMY,  DUMMY,  D

DUMMY,  DUMMY,  DUMMY,  E

DUMMY,  DUMMY,  DUMMY,  F

DUMMY,  DUMMY,  DUMMY,  G

DUMMY,  DUMMY,  DUMMY,  H

Note – you cannot open the document as user DUMMY because DUMMY is an invalid Access value, only ADMIN and USER entries are allowed to open the document.

Section Access Example 5

This adds Region to the restrictions as well as CostCentre, but the restriction on Region fails as the

region fieldname is not UPPERCASE.

Access,  UserID,  Password,  CostCentre,  Region

USER,  USER,  USER,  *,  *

ADMIN,  ADMIN,  ADMIN,  *,  *

USER,  JOHN,  JOHN,  A,  North

USER,  MARY,  MARY,  B,  South

USER,  PETER,  PETER,  C,  East

ADMIN,  SIMON,  SIMON,  A,  North

ADMIN,  SIMON,  SIMON,  A,  East

DUMMY,  DUMMY,  DUMMY,  D,  North

DUMMY,  DUMMY,  DUMMY,  E,  South

DUMMY,  DUMMY,  DUMMY,  F,  East

DUMMY,  DUMMY,  DUMMY,  G,  West

DUMMY,  DUMMY,  DUMMY,  H

Section Access Example 6

The Region fieldname is uppercase, but the restriction on Region still fails as the region data is not UPPERCASE.

Section Access Example 7

The Region restriction now works by loading the region data into a second field named REGION_SA and forcing the data in REGION_SA to UPPERCASE.

Version3 - The Region field has been removed from the Section Access table. This fixes the error identified by kanhomcake (March 27 2014).

Hopefully this will help avoid some of the basic errors that can be made when implementing Section Access.

Colin Albert.

Working hours calculation is essential measure which is useful in many business cases.  In this post I am going to demonstrate the steps to calculate this measure.  I have also covered other aspects related to working hours like overtime hours and excluding lunch break hours.  

Example in this post works for weekends as Saturday and Sunday. If you have any other weekends then refer to my below post

Working-days-and-hours-calculations-for-custom-weekends

Consider the below case

Let's say one Ticket is logged into the system on certain day , call it as Start Date, and the same Ticket got resolved on certain day, call it as End Day. Now we may want to calculate the working hours between these two Dates to calculate the efficiency of ticket closure time.

1) Calculate business working hours excluding week ends(Saturday and Sunday) and Holidays.

Below are the considerations

1) Count only standard working hours ( 9 AM - 6 PM)

2) Exclude Saturdays and Sundays

3) Exclude Holidays

Below is the Setup in QlikView

You can change the Date format of the below variables as per your convenience eg. 'DD/MM/YYYY hh:mm:ss' to 'DD-MMM-YYYY hh:mm:ss'

SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss';

First, convert your Timestamp date format to 24 hour format using the below functions

a) Timestamp# ()    (if your Timestamp values are text) 

b) TimeStamp ()     (If your Timestamp values are in proper Timestamp format)

Now you can setup the below variables for standard working hours in weekdays.You can change the Variable according to your working hours (Here 9 AM - 6 PM) and rest calculation will be done automatically

Now Setup the Holiday list as below. You can use your own holiday list

Below is the logic to calculate the business working hours between to dates

Here is the output

2) Calculate business working hours excluding week ends(Saturday and Sunday), Lunch Breaks and Holidays.

Below are the considerations

1) Count only standard working hours ( 9 AM - 6 PM)

2) Exclude Saturdays and Sundays

3) Exclude Lunch Break (1 PM - 2PM)

4) Exclude Holidays

Set the Variables for standard working hours and lunch breaks. You can change the values according to your needs

Include the Holidays

Below is the logic to calculate the business working hours between two dates

Here is the output

If you want to present the working hours in hh:mm:ss format then you can use below logic after calculating working hours

=interval(Working_Hours/24,'hh:mm:ss')

Please see the attached QVW.

Feel free to provide any suggestions.

Hi All,

This document helps you in loading multiple excels and excel sheets with the name of the sheet and data.

//to read each file from the specified folder

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

//In order to get the file information from SQLtables command making use of the ODBC connection format
ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

tables:
SQLtables;
DISCONNECT;

FOR i = 0 to NoOfRows('tables')-1
LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));
Table:
Load * ,
FileBaseName()as FIle,
FileDir() as Dir,
FileName() as File_Name,
'$(sheetName)' as Sheet_name
From $(file)(ooxml, embedded labels, table is [$(sheetName)]);

NEXT i

Next

Hope this helps !!!

please find the attachment for the eg: qvw and test fiels

Regards,

Avinashelite‌

I have two tables Table1,Table2. Table1 has DATE, Table2 has START_DT,END_DT. Want to join these two tables on DATE columns.Intervalmatch function will help us to make the join in Qlik. If we use left join, we can avoid synthetic tables too. Here is the example.If you want to test, prepare the test data.
Let me know if you need any more info/clarification.
Table1:
LOAD COUNTRY,
DATE,
C1,
C2
FROM Table1;
Table2:
LOAD COUNTRY, C3,C4,
date(START_DT) as START_DT,
date(END_DT) as END_DT
FROM Table2;
Final_Table:
INTERVALMATCH (DATE) left JOIN LOAD START_DT, END_DT RESIDENT Table2;
left Join (Table1) LOAD distinct * RESIDENT Table2;
DROP TABLE Table2;

We were looking for a solution to update Excel file pivottables before they are loaded into QlikView. I came up with the macro script below, which I want to share with you. I added an extra option to run the Workbook_Open macro. When opening an Excel file via QlikView, this macro will not start (to my experience). Choosing action option 2 will run the macro from QlikView, therefore starting whatever process is started when you open the Excel file manually.

In QV press Ctrl+M to add the macro to the QV document. Be sure to allow system access for this macro function to work. Check the example script in the header of the function. Be aware that the filename should be quoted with single quotes.

Please click the Like link if you are going to use this macro.

' *****************************************************
' Description: Function to open an Excel document and
'              refresh all external data queries and
'              pivottables in the Excel document or
'              run the Workbook_Open macro that runs
'              when manually opening the Excel document.
' Parameters:
' fileName > Specify the Excel document path to open.

'            The path should be the absolute (complete) path on the filesystem.
' action   > 1 = Open Excel file and refresh all external data tables.
'          > 2 = Open Excel file and run Workbook_Open macro.
'
' Example:
' LET result = OpenExcel('<path>\filename.xlsx', 1);
' If $(result) Then
'      LOAD * FROM '<path>\filename.xlsx';
' EndIf
' *****************************************************
Function OpenExcel(fileName, action)
Dim objExcel, objWb, fso

    ' Get file extenstion from the document name
    ext = Mid(fileName, InStrRev(fileName, ".") + 1)

    ' Check if specified document name is an Excel document
    If ext <> "xlsx" And ext <> "xlsm" And ext <> "xls" Then
        OpenExcel = False
        Exit Function
    End If

    ' Create a filesystem instance
    Set fso = CreateObject("Scripting.FileSystemObject")
    ' Exit the function if the documentdoes not exist
    If Not fso.FileExists (fileName) Then
        OpenExcel = False
        Exit Function
    End If

    ' Create an Excel instance
    Set objExcel = CreateObject("Excel.Application")
    ' Exit the function if Excel object cannot be created. Excel is not installed?
    If objExcel Is Nothing Then
        OpenExcel = False
        Exit Function
    End If

    ' Disable Excel prompts and alerts
    objExcel.DisplayAlerts = False

    ' Open the Excel document
    Set objWb = objExcel.Workbooks.Open(fileName)
    ' Exit the function if document cannot be opened. Is the document already opened?
    If objWb Is Nothing Then
        OpenExcel = False
        Exit Function
    End If

    ' Do some stuff according to the specified action parameter
    Select Case action
    Case 1
        ' For Excel 2007 and later documents refresh all external data sources
        ' and pivottables in the document
        If ext = "xlsx" Or ext = "xlsm" Then
            objWb.RefreshAll

        ' For Excel 2003 document refresh each pivottable on each sheet
        ElseIf ext = "xls" Then
            ' First refresh each external query table on all sheets.
            ' Pivottables may depend on these query tables
            For Each sh in objWb.Worksheets
                For Each qt In sh.QueryTables
                    qt.Refresh
                Next
            Next
            ' Refresh every pivottable on all sheets
            For Each sh in objWb.Worksheets
                For Each pvt In sh.PivotTables
                    pvt.RefreshTable
                Next
            Next
        End If

    Case 2

        ' Run the Workbook_Open macro
        objWb.Application.Run "ThisWorkbook.Workbook_Open"
    End Select

    ' Save Excel document and quit Excel
    objWb.Close True
    objExcel.Quit

    ' Cleanup memory
    Set fso = Nothing
    Set objWb = Nothing
    Set objExcel = Nothing

    ' Return true if the function finishes without an error.
    ' This does not mean that the Excel refresh was succesful
    OpenExcel = True
End Function

Hi All,

The below script helps to remove the empty columns (which has all null values) dynamically from the DataModel.  To use just copy the below script and paste at the end of your script.

FOR a = 0 TO NoOfTables() - 1
    LET vTable = TableName($(a));
    LET d = 0;
    FOR i = 1 TO NoOfFields('$(vTable)')
        LET j = i - d;
        LET vField = FieldName($(j), '$(vTable)');
        LET vFieldValueCount = Alt(FieldValueCount('$(vField)'), 0);
        IF (vFieldValueCount <= 0 ) THEN
         DROP FIELD [$(vField)] FROM $(vTable);
         TRACE DROP FIELD [$(vField)] FROM $(vTable);
         LET d = d + 1; // dropping fields impacts the internal field no.
        END IF
       NEXT i
    NEXT a

If you want the empty values (whitespaces) then use below script

FOR a = 0 TO NoOfTables() - 1
  LET vTable = TableName($(a));
  LET d = 0;
  FOR i = 1 TO NoOfFields('$(vTable)')
  LET j = i - d;
  LET vField = FieldName($(j), '$(vTable)');
  DropField:
  LOAD 1 as Check2
  Resident $(vTable)
  WHERE Len(Trim($(vField))) > 0;

  LET vNoOfRows = NoOfRows('DropField');
  DROP TABLE  DropField;
  IF vNoOfRows = 0  THEN
  DROP FIELD [$(vField)] FROM $(vTable);
  TRACE DROP FIELD [$(vField)] FROM $(vTable);
  LET d = d + 1; // dropping fields impacts the internal field no.
  END IF       
  NEXT i
NEXT a

You can test the above script using below Inline table.

Data:

LOAD

*,

' ' AS Field3,

Null() AS Field4

INLINE [

Field1, Field2

1,2

3,4

5,6];

When you use the first scrip then Field4 will be dropped, and when you use second script then both Field3 and Field4 are dropped.

Hope this helps.

Regards,

jagan.