Thursday, July 7, 2011

Creating Pivot Table in Qlikview to display Account Receivable Outstanding Amount from Lawson M3 data

In my last blog, I showed how to build a straight table using the Lawson M3 AR data. Continuing with the same document,  I will show how to create Pivot Table showing the outstanding amount in 0-30-60 days breakup. 

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 Pivot Table Outstanding Amount in the Window Title Text Box, and select the  Pivot table in the chart type (last icon on First row) .

Click Next button to bring the next window Dimensions. Here we can select the Dimension fields to be displayed in the pivot table. Highlight the fields CustomerNumber and CustomerName  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(If(DueDays <= 0 ,OutStandingAmount,0)) in the box and click OK button .

Object 3-1

 

Add additional 3 more Expression as shown below.

Object 3-2

Object 3-3

Object 3-4

 

Add the Labels to all the four expressions

Object 4

 

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 for all the expression as shown below.

 

Object 5

 

Click finish to display the Pivot table.

 

Object 3-6

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.

Thursday, December 18, 2008

Cross Reference System Table in Movex/M3

One of the most important tables in Movex/M3 is CSYTAB, the cross reference system table. Lot of the cross reference programs (CRSxxx) stores data in CSYTAB.

The primary key for CSYTAB is the following fields:
CTCONO Company
CTDIVI Division
CTSTCO Constant Value
CTSTKY Key Value
CTLNCD Language

The following SQL statement will show all the records entered in Movex/M3 program or function CRS040 (Item Type. Open):

Select CTSTCO, CTSTKY, CTTX15, CTTX40 from CSYTAB where
CTSTCO = 'ITTY'

CTSTCO CTSTKY CTTX15 CTTX40
ITTY 010 Raw materials Raw materials
ITTY 030 Finished Goods Finished Goods
ITTY 040 Packaging Packaging
ITTY 070 Subcontract Subcontract

The CTSTCO indicated the Constant Value referencing to a group of values. The value ‘ITTY’ in CTSTCO indicates for item type.

The field CTSTKY indicates a key value, which is a value included in the group of Constant Value CTSTCO.

The SQL statement “Select distinct CTSTCO from CSYTAB” will list all the Constant value used in your system.

Some Cross functional programs will use the field CTDIVI, allowing different values to be entered at Division level.

Cross function programs like CRS070 (Delivery method. Open) will use CTLNCD to allow entering same key value for different Language. The SQL statement below includes the CTLNCD:

Select CTSTCO, CTSTKY, CTLNCD, CTTX15, CTTX40 from CSYTAB where CTSTCO = 'MODL'
CTSTCO CTSTKY CTLNCD CTTX15 CTTX40
MODL A00 GB LTL shipment LTL shipment
MODL ONS GB On-Site On-Site

Monday, December 1, 2008

WPF - Working with XML file using Linq to XML

Building WPF ListView application to read XML file using Linq to XML

Recently I published my first article on codeproject.com and the link access article is http://www.codeproject.com/KB/WPF/WPFandXML.aspx

The article discuss on building a user interface using Microsoft 's Windows Presentation Foundation (WPF) using customer master table as datasource.

Let me know your comments and feedback on the article.

Wednesday, November 26, 2008

Configure ODBC to access iSeries(AS/400) system

IBM provides software like WRKQRY, STRSQL on the iSeries (AS/400) system to access and query the database DB2 UDB and needs green screen terminal emulation to work with it Another way to access the DB2 database is to use iSeries navigator that provides option to run SQL statements.

The iSeries database can also be accessed using the ODBC and OLE DB drivers that can be installed on the PC and configured in the ODBC data source administration. Once the drivers are installed and configured you can use SQL to access and query the DB2 database using Excel or Access or any third party database management tool like WinSQL (http://www.synametrics.com/SynametricsWebApp/WinSQL.jsp).

The OLE DB drivers allows developer to write application using OLE DB connection in VB or VB.NET/C# using Microsoft Visual studio etc.

To install ODBC or OLE DB driver, you will require the installation media iSeries Access for Window from IBM and is normally provided as a part of standard softwares when iSeries system are purchased from IBM. If you do not have the installation media contact the vendor or IBM for more information and cost involved in purchasing it.

During the installation process of iSeries Access for Window software, select the custom setup that allows to select the required program features you want to install on the PC. Below is the screenshot when performing custom setup and make sure you have selected the ODBC and OLE DB provider as required for installation.
Once the installation is complete, the drivers will be available for use for your tools or development.












To configure ODBC to access perform the following step (I am using Window XP):
Click on Start->Control Panel->Administrative Tools that will bring the Window to setup a Data Source on your PC.















Open the Data Source (ODBC) by double click on the shortcut to bring up the ODBC Data Source Administrator.
Select the System DSN and Click on the Add button to create a new Data source.















Select Client Access ODBC Driver (32-bit) and click on the Finish button to continue.

Enter a name in the Data source name field and the IP address in the System field.
















Click on the Server tab and enter the SQL default library.
















Click OK button to complete the process.

For additional references to the other options and tabs refer the documentation or user guide of the iSeries Access to Windows.

Once the setup is complete, the ODBC data source is available to access from other tools.
In next time post I will write on tools to access the ODBC data source.