Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Incrementaal load not success
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Incrementaal load not success
Hi,
Implemented the incremental load. Data is not fetching the updated records in report.
Fact Table:
LET vSQLSection = '
SELECT
BAL.CODE_COMBINATION_ID,
LED.LEDGER_ID,
LED.NAME LEDGER_NAME,
BAL.Last_Update_Date,
(CASE WHEN led.ledger_category_code = ' & chr(39) & 'PRIMARY' & chr(39) & ' then ' & chr(39) & 'Primary' & chr(39) & ' else ' & chr(39) & 'Reporting' & chr(39) & ' end) Ledger_type,
PERIOD_NAME,
NVL (bal.begin_balance_dr, 0) - NVL (bal.begin_balance_cr, 0) Begin_USD,
(nvl(bal.period_net_dr,0) - nvl(bal.period_net_cr,0)) NET_USD,
NVL (bal.begin_balance_dr, 0) - NVL (bal.begin_balance_cr, 0)
+ NVL(bal.period_net_dr, 0) - NVL (bal.period_net_cr, 0) END_USD
FROM
GL_BALANCES BAL,
GL_LEDGERS LED
where 1=1
AND LED.LEDGER_ID = BAL.LEDGER_ID
AND LED.LEDGER_CATEGORY_CODE IN(' & chr(39) & 'PRIMARY' & chr(39) & ',' & chr(39) & 'ALC' & chr(39) & ')
AND BAL.TRANSLATED_FLAG IS NULL
--and bal.period_name =' & chr(39) & 'JAN-16' & chr(39) & '
--AND BAL.LEDGER_ID =2235 --consolidate ledger
and BAL.CURRENCY_CODE =' & chr(39) & 'USD' & chr(39) & '
and BAL.ACTUAL_FLAG =' & chr(39) & 'A' & chr(39) & '
';
SET vTableName = 'GL_Balances';
LET vUniqueKey = 'LEDGER_ID & CODE_COMBINATION_ID & PERIOD_NAME';
SET vOutputTableName = 'Fact - GL Balances';
SET vLastUpdateFieldName = 'BAL.Last_Update_Date';
Call IncrementalLoad ('$(vTableName)', '$(vSQLSection)', '$(vUniqueKey)','$(vOutputTableName)','$(vLastUpdateFieldName)') ;
Below is the logic implemented
sub IncrementalLoad (vTableName, vSQLSection, vUniqueKey, vOutputTableName, vLastUpdateField)
Trace('$(vDataFolder)$(vOutputTableName).qvd');
Let v_Incremental_qvd = isNull(QvdCreateTime('$(vDataFolder)$(vOutputTableName).qvd'));
If (v_Incremental_qvd = -1) then
Trace(Beginning Full Load for $(vTableName));
LET v_SQL = '$(vSQLSection)';
$(vTableName):
Load
*,
$(vUniqueKey) as key_Unique;
SQL $(v_SQL);
RENAME field LAST_UPDATE_DATE to Last_Update_Date;
ELSE
Trace(Beginning Incremental Load for $(vTableName) on $(vSite));
// get dates
Dates:
Load distinct Last_Update_Date
from $(vDataFolder)$(vOutputTableName).qvd (QVD);
//exit script;
GetMaxDate:
Load Max(Last_Update_Date) as MaxRecordDate
Resident Dates;
LET v_MaxRecordDate = date(Peek('MaxRecordDate', 0, 'GetMaxDate'));
Drop Tables GetMaxDate, Dates;
LET v_SQL = '$(vSQLSection) AND $(vLastUpdateField) >= '& chr(39) & v_MaxRecordDate & chr(39);
Trace($(v_SQL));
temp:
NoConcatenate
Load
*,
$(vUniqueKey) as key_Unique;
SQL $(v_SQL);
$(vTableName):
NoConcatenate
LOAD *
from $(vDataFolder)$(vOutputTableName).qvd (QVD)
where Not Exists(key_Unique)
;
Concatenate
Load * RESIDENT temp;
Drop Table temp;
ENDIF
Store $(vTableName) into $(vDataFolder)$(vOutputTableName).qvd;
Drop Table $(vTableName);
end sub
After reload we observed in where class last_update_date checking ' ' instead of latest date
SET vTableName = 'GL_Balances'
LET vUniqueKey = 'LEDGER_ID & CODE_COMBINATION_ID & PERIOD_NAME'
SET vOutputTableName = 'Fact - GL Balances'
SET vLastUpdateFieldName = 'BAL.Last_Update_Date'
Call IncrementalLoad ('GL_Balances', '
SELECT
BAL.CODE_COMBINATION_ID,
LED.LEDGER_ID,
LED.NAME LEDGER_NAME,
BAL.Last_Update_Date,
(CASE WHEN led.ledger_category_code = ''PRIMARY'' then ''Primary'' else ''Reporting'' end) Ledger_type,
PERIOD_NAME,
NVL (bal.begin_balance_dr, 0) - NVL (bal.begin_balance_cr, 0) Begin_USD,
(nvl(bal.period_net_dr,0) - nvl(bal.period_net_cr,0)) NET_USD,
NVL (bal.begin_balance_dr, 0) - NVL (bal.begin_balance_cr, 0)
+ NVL(bal.period_net_dr, 0) - NVL (bal.period_net_cr, 0) END_USD
FROM
GL.GL_BALANCES BAL,
GL.GL_LEDGERS LED
where 1=1
AND LED.LEDGER_ID = BAL.LEDGER_ID
AND LED.LEDGER_CATEGORY_CODE IN(''PRIMARY'',''ALC'')
AND BAL.TRANSLATED_FLAG IS NULL
--and bal.period_name =''JAN-16''
--AND BAL.LEDGER_ID =2235 --consolidate ledger
and BAL.CURRENCY_CODE =''USD''
and BAL.ACTUAL_FLAG =''A''
', 'LEDGER_ID & CODE_COMBINATION_ID & PERIOD_NAME','Fact - GL Balances','BAL.Last_Update_Date')
Trace('D:\QVServer\FinancePOC\QVD\Fact - GL Balances.qvd')
('D:\QVServer\FinancePOC\QVD\Fact - GL Balances.qvd')
Let v_Incremental_qvd = isNull(QvdCreateTime('D:\QVServer\FinancePOC\QVD\Fact - GL Balances.qvd'))
If (v_Incremental_qvd = -1) then
Trace(Beginning Incremental Load for GL_Balances on )
(Beginning Incremental Load for GL_Balances on )
Dates:
Load distinct Last_Update_Date
from D:\QVServer\FinancePOC\QVD\Fact - GL Balances.qvd (QVD)
1 fields found: Last_Update_Date,
1 lines fetched
GetMaxDate:
Load Max(Last_Update_Date) as MaxRecordDate
Resident Dates
1 fields found: MaxRecordDate,
1 lines fetched
LET v_MaxRecordDate = date(Peek('MaxRecordDate', 0, 'GetMaxDate'),'DD-MMM-YYYY')
Drop Tables GetMaxDate, Dates
LET v_SQL = '
SELECT
BAL.CODE_COMBINATION_ID,
LED.LEDGER_ID,
LED.NAME LEDGER_NAME,
BAL.Last_Update_Date,
(CASE WHEN led.ledger_category_code = ''PRIMARY'' then ''Primary'' else ''Reporting'' end) Ledger_type,
PERIOD_NAME,
NVL (bal.begin_balance_dr, 0) - NVL (bal.begin_balance_cr, 0) Begin_USD,
(nvl(bal.period_net_dr,0) - nvl(bal.period_net_cr,0)) NET_USD,
NVL (bal.begin_balance_dr, 0) - NVL (bal.begin_balance_cr, 0)
+ NVL(bal.period_net_dr, 0) - NVL (bal.period_net_cr, 0) END_USD
FROM
GL.GL_BALANCES BAL,
GL.GL_LEDGERS LED
where 1=1
AND LED.LEDGER_ID = BAL.LEDGER_ID
AND LED.LEDGER_CATEGORY_CODE IN(''PRIMARY'',''ALC'')
AND BAL.TRANSLATED_FLAG IS NULL
--and bal.period_name =''JAN-16''
--AND BAL.LEDGER_ID =2235 --consolidate ledger
and BAL.CURRENCY_CODE =''USD''
and BAL.ACTUAL_FLAG =''A''
AND BAL.Last_Update_Date >= '& chr(39) & v_MaxRecordDate & chr(39)
Trace(
SELECT
BAL.CODE_COMBINATION_ID,
LED.LEDGER_ID,
LED.NAME LEDGER_NAME,
BAL.Last_Update_Date,
(CASE WHEN led.ledger_category_code = 'PRIMARY' then 'Primary' else 'Reporting' end) Ledger_type,
PERIOD_NAME,
NVL (bal.begin_balance_dr, 0) - NVL (bal.begin_balance_cr, 0) Begin_USD,
(nvl(bal.period_net_dr,0) - nvl(bal.period_net_cr,0)) NET_USD,
NVL (bal.begin_balance_dr, 0) - NVL (bal.begin_balance_cr, 0)
+ NVL(bal.period_net_dr, 0) - NVL (bal.period_net_cr, 0) END_USD
FROM
GL.GL_BALANCES BAL,
GL.GL_LEDGERS LED
where 1=1
AND LED.LEDGER_ID = BAL.LEDGER_ID
AND LED.LEDGER_CATEGORY_CODE IN('PRIMARY','ALC')
AND BAL.TRANSLATED_FLAG IS NULL
--and bal.period_name ='JAN-16'
--AND BAL.LEDGER_ID =2235 --consolidate ledger
and BAL.CURRENCY_CODE ='USD'
and BAL.ACTUAL_FLAG ='A'
AND BAL.Last_Update_Date >= '')
(
SELECT
BAL.CODE_COMBINATION_ID,
LED.LEDGER_ID,
LED.NAME LEDGER_NAME,
BAL.Last_Update_Date,
(CASE WHEN led.ledger_category_code = 'PRIMARY' then 'Primary' else 'Reporting' end) Ledger_type,
PERIOD_NAME,
NVL (bal.begin_balance_dr, 0) - NVL (bal.begin_balance_cr, 0) Begin_USD,
(nvl(bal.period_net_dr,0) - nvl(bal.period_net_cr,0)) NET_USD,
NVL (bal.begin_balance_dr, 0) - NVL (bal.begin_balance_cr, 0)
+ NVL(bal.period_net_dr, 0) - NVL (bal.period_net_cr, 0) END_USD
FROM
GL.GL_BALANCES BAL,
GL.GL_LEDGERS LED
where 1=1
AND LED.LEDGER_ID = BAL.LEDGER_ID
AND LED.LEDGER_CATEGORY_CODE IN('PRIMARY','ALC')
AND BAL.TRANSLATED_FLAG IS NULL
--and bal.period_name ='JAN-16'
--AND BAL.LEDGER_ID =2235 --consolidate ledger
and BAL.CURRENCY_CODE ='USD'
and BAL.ACTUAL_FLAG ='A'
AND BAL.Last_Update_Date >= '')
temp:
NoConcatenate
Load
*,
LEDGER_ID & CODE_COMBINATION_ID & PERIOD_NAME as key_Unique
SQL
SELECT
BAL.CODE_COMBINATION_ID,
LED.LEDGER_ID,
LED.NAME LEDGER_NAME,
BAL.Last_Update_Date,
(CASE WHEN led.ledger_category_code = 'PRIMARY' then 'Primary' else 'Reporting' end) Ledger_type,
PERIOD_NAME,
NVL (bal.begin_balance_dr, 0) - NVL (bal.begin_balance_cr, 0) Begin_USD,
(nvl(bal.period_net_dr,0) - nvl(bal.period_net_cr,0)) NET_USD,
NVL (bal.begin_balance_dr, 0) - NVL (bal.begin_balance_cr, 0)
+ NVL(bal.period_net_dr, 0) - NVL (bal.period_net_cr, 0) END_USD
FROM
GL.GL_BALANCES BAL,
GL.GL_LEDGERS LED
where 1=1
AND LED.LEDGER_ID = BAL.LEDGER_ID
AND LED.LEDGER_CATEGORY_CODE IN('PRIMARY','ALC')
AND BAL.TRANSLATED_FLAG IS NULL
--and bal.period_name ='JAN-16'
--AND BAL.LEDGER_ID =2235 --consolidate ledger
and BAL.CURRENCY_CODE ='USD'
and BAL.ACTUAL_FLAG ='A'
AND BAL.Last_Update_Date >= ''
10 fields found: CODE_COMBINATION_ID, LEDGER_ID, LEDGER_NAME, LAST_UPDATE_DATE, LEDGER_TYPE, PERIOD_NAME, BEGIN_USD, NET_USD, END_USD, key_Unique,
0 lines fetched
GL_Balances:
NoConcatenate
LOAD *
from D:\QVServer\FinancePOC\QVD\Fact - GL Balances.qvd (QVD)
where Not Exists(key_Unique)
11 fields found: CODE_COMBINATION_ID, LEDGER_ID, LEDGER_NAME, Last_Update_Date, LEDGER_TYPE, PERIOD_NAME, BEGIN_USD, NET_USD, END_USD, key_Unique, LAST_UPDATE_DATE,
10 lines fetched
Concatenate
Load * RESIDENT temp
10 fields found: CODE_COMBINATION_ID, LEDGER_ID, LEDGER_NAME, LAST_UPDATE_DATE, LEDGER_TYPE, PERIOD_NAME, BEGIN_USD, NET_USD, END_USD, key_Unique,
10 lines fetched
Drop Table temp
ENDIF
Store GL_Balances into D:\QVServer\FinancePOC\QVD\Fact - GL Balances.qvd
Drop Table GL_Balances
end sub
Could you please give an idea how to fix the incremental load.