Wednesday, June 29, 2011

Create Straight Table in Qlikview to display Account Receivable Outstanding Amount

In my last blog posting http://shivakamathblog.blogspot.com/2011/06/open-outstanding-account-receivable-ar.html , I showed in Qlikview, how to load data from the Lawson M3(Movex) system using the sales ledger table FSLEDG and customer master table OCUSMA. Once the data is load and saved in the Qlikview document, we can use the data to display in different Qlikview objects like table,list or charts. We will create a simple Straight table object to display the data.

In the worksheet right click on the mouse button to bring the popup context menu, and click on New Sheet Object –> Chart to bring the Chart object designer as shown below.

Object 1

Type Outstanding Amount in the Window Title Text Box, and select the straight table in the chart type (last icon on second row) .

Click Next button to bring the next window Dimensions. Here we can select the Dimension fields to be displayed in the straight table. Highlight the required fields in the Available Fields/Group box, and click Add. The highlighted fields will be moved into the Used Dimension box.

 

Object 2

 

Click the next button to bring the Expression Window and will display a popup window Edit Expression. Type Sum(OutStandingAmount)  in the box and click OK button . Here we are adding the out standing amount to be displayed.

 

Object 3

 

Type Outstanding Amount in the Label box.

Object 3-2

 

Click the next buttons and we will accept defaults in the Sort, Presentation and Visual Cues,Style windows.

On the number windows, select Money for Number Format Setting as shown below.

Object 4

Click on the Finish button to complete and the straight table will be displayed with the dimension fields and the calculated amount fields.Object 5

Monday, June 27, 2011

Open outstanding Account Receivable (AR) from Lawson M3 (Movex) system in Qlikview

Today, I will show how to load the data in Qlikview from the Lawson M3 (Movex system).  Please refer to my previous posting for the SQL  statement http://shivakamathblog.blogspot.com/2008/11/open-outstanding-ar-from-lawson-m3.html.

Start the Qlikview application and open the script editor. Add the connection to the AS/400 or iseries ( on any other data source for M3). Check my previous posting for more details http://shivakamathblog.blogspot.com/2011/06/loading-data-from-as400iseriesi5os-into.html for more details.

Enter the following statements in the script window after the connection statement:

OutStanding:
Load     ESCONO as Company,
        ESDIVI as Division,
        ESCUNO as CustomerNumber,
        ESINYR as InvoiceYear,
        ESCINO as InvoiceNumber,
        Date(Date#(ESDUDT,'YYYYMMDD')) as DueDate,
        Today() - Date(Date#(ESDUDT,'YYYYMMDD')) as DueDays,
        Date(Date#(ESIVDT,'YYYYMMDD')) as InvoiceDate,
        ESCUAM as OutStandingAmount,
        OKCUNM as CustomerName,
        OKCRL2 as CreditLimit2,
        OKCRL3 as CreditLimit3 ;
SQL
with CTE_AR as
(
SELECT ESCONO,ESDIVI, ESCUNO, ESINYR,ESCINO, 
MAX(ESDUDT) AS ESDUDT, MAX(ESIVDT) AS ESIVDT, SUM (ESCUAM) AS ESCUAM
FROM M3LIBRARY.FSLEDG
GROUP BY ESCONO,ESDIVI, ESCUNO, ESINYR, ESCINO HAVING SUM (ESCUAM) <> 0
)
select ESCONO,ESDIVI, ESCUNO, ESINYR,ESCINO, ESDUDT,ESIVDT,ESCUAM,
OKCUNM,OKCRL2,OKCRL3
from  CTE_AR
LEFT JOIN M3LIBRARYNAME.OCUSMA ON ESCONO=OKCONO AND ESCUNO=OKCUNO ;

 

 

The statements is made up of two part:

One will read the data from the M3 data source

SQL
with CTE_AR as
(
SELECT ESCONO,ESDIVI, ESCUNO, ESINYR,ESCINO,
MAX(ESDUDT) AS ESDUDT, MAX(ESIVDT) AS ESIVDT, SUM (ESCUAM) AS ESCUAM
FROM M3LIBRARYNAME.FSLEDG
GROUP BY ESCONO,ESDIVI, ESCUNO, ESINYR, ESCINO HAVING SUM (ESCUAM) <> 0
)
select ESCONO,ESDIVI, ESCUNO, ESINYR,ESCINO, ESDUDT,ESIVDT,ESCUAM,
OKCUNM,OKCRL2,OKCRL3
from CTE_AR
LEFT JOIN M3LIBRARYNAME.OCUSMA ON ESCONO=OKCONO AND ESCUNO=OKCUNO ;

 

The second part will load data from succeeding input table. 

OutStanding:
Load ESCONO as Company,
ESDIVI as Division,
ESCUNO as CustomerNumber,
ESINYR as InvoiceYear,
ESCINO as InvoiceNumber,
Date(Date#(ESDUDT,'YYYYMMDD')) as DueDate,
Today() - Date(Date#(ESDUDT,'YYYYMMDD')) as DueDays,
Date(Date#(ESIVDT,'YYYYMMDD')) as InvoiceDate,
ESCUAM as OutStandingAmount,
OKCUNM as CustomerName,
OKCRL2 as CreditLimit2,
OKCRL3 as CreditLimit3 ;

Here we are performing following additional steps:

  • Rename all the fields
  • Convert the M3 date fields into Qlikview dates type
  • Add calculated field DueDays.

 

When the script is reload, it will create a table Outstanding that can be used with Qlikview objects.

M3 AR outstanding table

 

Once the data is reloaded, save the Qlikview file. In the next blog I will discuss on creating table charts and other objects using the loaded data.

Tuesday, June 7, 2011

Loading data from AS/400(iseries,i5/OS) into Qlikview

Qlikview is an in-memory management BI technology allows to connect and combine data from different data sources. You can download and install a free personal edition from the website http://www.qlikview.com/. I will be posting about my experience in working with Qlikview.


Qlikview can connect to AS/400 (iSeries,i5/OS) and reload data using the SQL scripts. You will need to install client access for iSeries i5/OS from IBM and refer to my previous post http://shivakamathblog.blogspot.com/2008/11/configure-odbc-to-access-iseriesas400.html for more details.


Once the Qlikview and iSeries access is installed we can connect to AS/400 using the OLE DB provider. Start the Qlikview application and open a new document from the File menu. Open the scripting window using the option Edit Script… on the File Menu. At the bottom of the scripting window, select the Data tab as shown below


OLE DB Connect


From the Database, select the OLE DB from the dropdown list and click on the connect button. This will open a Data link Properties dialog box:


OLE DB Dialog


Select the IBM DB2 UDB for iSeries IBMDASQL OLE DB Provider, and click on the connection tab:


OLE DB Server


On the Data Source textbox, enter the Server name or the IP address of the AS/400 machine. Enter the AS/400 sign on user name and password and the Catalog name of the AS/400. Please contact your AS/400 administrator for any help. Make sure “Allow saving password” is checked and “Blank password” is unchecked.
Click OK button and it will insert the connection string (starting with OLEDB Connect …. ) in the scripting window:



OLE DB String 0


After the connection string, type the SQL statement as required. Click the Save option from the Toolbar and then Click OK button below to close the scripting window.
From the File menu, select Reload option to run the script. After reloading is finished, the data is available in Qlikview document to create objects like Charts or Lists.



In the next posting, I will write on creating the charts and other objects.