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