Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am loading data incrementally.
My data is on monthly level in database and MONTH column as character as 01JAN2016,01FEB2016,01MAR2016 and so on.
I want to load the data only latest month data for each load.If I load twice in the same month, data should be deleted only for that month and load latest data.
I am using this code.
Please advise if it wrong.
LET lastReloadTime = date(MONTHSTART(today()),'DDMMMYYYY');
//LET lastReloadTime = num(date(ReloadTime(),'DDMMMYYYY'));
SALES:
LOAD EMPNO,
ENAME,
SALES,
DATE(MONTH,'DDMMMYY') AS MONTH
FROM
Incremental.xlsx
(ooxml, embedded labels, table is DATA)
where MONTH>='$(lastReloadTime)';
CONCATENATE
LOAD EMPNO,
ENAME,
SALES,
MONTH
FROM
SALES.QVD
(qvd)
WHERE NOT exists (EMPNO);
STORE SALES into SALES.QVD(QVD);
Data:
Load * line [
EMPNO,ENAME,SALES,MONTH
1,E1,10,01Jan16
2,E2,5,01Jan16
3,E3,115,01Jan16
4,E4,15,01Jan16
5,E5,18,01Jan16
6,E6,20,01Jan16
7,E7,23,01Jan16
8,E8,25,01Jan16
9,E9,28,01Jan16
10,E10,30,01Oct16
11,E11,33,01Oct16
12,E12,35,01Oct16
13,E13,38,01Jan16
14,E14,40,01Jan16
15,E15,43,01Nov16
16,E16,45,01Nov16
];
Hi,
Try this
LET lastReloadTime = DATE(MONTHSTART(ReloadTime()),'DDMMMYYYY');
SALES:
LOAD EMPNO,
ENAME,
SALES,
DATE(MONTH,'DDMMMYY') AS MONTH
FROM
Incremental.xlsx
(ooxml, embedded labels, table is DATA)
where MONTH >= '$(lastReloadTime)';
CONCATENATE
LOAD EMPNO,
ENAME,
SALES,
MONTH
FROM
SALES.QVD
(qvd)
WHERE MONTH < '$(lastReloadTime)';
STORE SALES into SALES.QVD(QVD);
EXIT Script;
Hope this helps you.
Regards,
jagan.
Hi,
Try this
LET lastReloadTime = DATE(MONTHSTART(ReloadTime()),'DDMMMYYYY');
SALES:
LOAD EMPNO,
ENAME,
SALES,
DATE(MONTH,'DDMMMYY') AS MONTH
FROM
Incremental.xlsx
(ooxml, embedded labels, table is DATA)
where MONTH >= '$(lastReloadTime)';
CONCATENATE
LOAD EMPNO,
ENAME,
SALES,
MONTH
FROM
SALES.QVD
(qvd)
WHERE MONTH < '$(lastReloadTime)';
STORE SALES into SALES.QVD(QVD);
EXIT Script;
Hope this helps you.
Regards,
jagan.
Hi Jagan,
Seems it is not working.
Please help to check with the attached data. !
Hi,
There is no data for current month, may be that is the reason I think you are not getting the solution. I just added a dummy record for October. Attached the QVD, QVW file for your reference.
Regards,
Jagan.
Thank You Jagan.
My actual data source is a database.
Will the below syntax work ?
.
.
.
FROM
Incremental.xlsx
(ooxml, embedded labels, table is DATA)
where MONTH >= '$(lastReloadTime)';
How you are filtering in your select query? Is below query works inDB?
SELECT
*
FROM TableName
WHERE MONTH >= '01Oct16';
Is this query working in DB?
Regards,
Jagan.
SELECT
*
FROM TableName
WHERE MONTH >= '01Oct16';
This is not working.
TRY this
SELECT
*
FROM TableName
WHERE MONTH >= '01-OCT-2016'
Or try changing to other formats.
Regards,
Jagan.