Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all ,
I have this SQL statement I Wanted to apply incremental load on the same TRX_DATE is a transaction date for the fact table
how to do this please guide ?
Select
[MERCHANT_ID], b.ACCOUNT_ID
,[TERMINAL_ID]
,[BATCH_ID]
,[STAFF_ID]
,[AMOUNT]
,[RATE]
,[VOLUME]
,[VEHICLE_NO]
,[TRX_DATE]
,[SETTLEMENT_DATE]
,[NOZZLE_ID]
,[VEHICLE_SEGMENT]
,[TOTALIZER_READING]
,[TRX_START_DATE]
,[PRESET_TYPE]
,[PRESET_VALUE]
,b.AUTOMATION_TYPE
,b.ME_SAPCC as CCNO
,b.ME_NAME
,b.ME_STP
,b.ME_CITY
FROM [RA].[dbo].[RO_TRANSACTION_DETAILS] a
Inner Join [RA].[dbo].[RA_ACCOUNTS] b ON a.MERCHANT_ID = b.ACCOUNT_ID ;--b.ACCOUNT_TYPE='SA';
--where TRX_DATE >= '01-OCT-2017' and TRX_DATE <= '2-Oct-2017';
Vikas
Normal Incremental Load script and format the Date according to that of TRX_DATE.
where TRX_DATE >= '01-OCT-2017' and TRX_DATE <= '2-Oct-2017';
This line will have variables instead of Oct dates.
Hi Shraddha ,
In this table we have millions of records , seeking solutions for incremental load which will capture only
latest records while pulling from SQL.
Can you please share script or some example if you have ?
Vikas
You will have to fetch one tym records for some date.(Say you have fetch records till 2nd Oct 2017) and store it in qvd as Trx.Qvd
Try below:
MAXData:
Load
Max(TRX_DATE) as Last_Trx_Date
From .....Trx.qvd;
let vMinInc= num(peek('Last_Trx_Date'));
let vMaxInc=num(today());/* Max Date for inc load Load*/
Drop table MAXData;
/* Creating all dates from Min to Max */
Temp:
Load
date($(vMinInc) + RowNo() -1) as Date,
AutoGenerate 1
While date($(vMinInc) + RowNo() -1) < date($(vMaxInc));
/* Loading Temp table to calculate no of rows*/
TEMP:
Load Distinct
Date
Resident Temp;
Drop table Temp;
Let vCount = NoOfRows('TEMP'); /*calculate no of rows i.e no of Dates*/
For i=0 to $(vCount)-1; /* loop to start with concatenating all Daily data*/
let vDate = date(peek('Date',$(i),'TEMP'),'DD-MMM-YYYY');
TableName:
Select
[MERCHANT_ID], b.ACCOUNT_ID
,[TERMINAL_ID]
,[BATCH_ID]
,[STAFF_ID]
,[AMOUNT]
,[RATE]
,[VOLUME]
,[VEHICLE_NO]
,[TRX_DATE]
,[SETTLEMENT_DATE]
,[NOZZLE_ID]
,[VEHICLE_SEGMENT]
,[TOTALIZER_READING]
,[TRX_START_DATE]
,[PRESET_TYPE]
,[PRESET_VALUE]
,b.AUTOMATION_TYPE
,b.ME_SAPCC as CCNO
,b.ME_NAME
,b.ME_STP
,b.ME_CITY
FROM [RA].[dbo].[RO_TRANSACTION_DETAILS] a
Inner Join [RA].[dbo].[RA_ACCOUNTS] b ON a.MERCHANT_ID = b.ACCOUNT_ID ;--b.ACCOUNT_TYPE='SA';
--where TRX_DATE = '$(vDate)' ;
Next
Concatenate
Load *
From ....Trx.qvd;
Store Tablename into ....Trx.qvd;
Thanks I will try and let you know
sure