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.

2 comments:

Jacob Gabrie said...

Thanks for sharing your informative blog i would like to add about account receivable follow up it is a process of managing revenue so that all services bills will be appropriate and accurate.if all activities are documented properly ultimate payment will get increased..

Vikas Dhama said...

Thanks for sharing the information.

For more info : Account Receivables Follow-Up