Qlik Community

Qlik Sense Deployment & Management

Discussion board where members learn more about Qlik Sense Installation, Deployment and Management.

vikasmahajan
Honored Contributor III

Incremental Load from SQL table Simple

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

5 Replies
shraddha_g
Honored Contributor III

Re: Incremental Load from SQL table Simple

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.

vikasmahajan
Honored Contributor III

Re: Incremental Load from SQL table Simple

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

shraddha_g
Honored Contributor III

Re: Incremental Load from SQL table Simple

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;

vikasmahajan
Honored Contributor III

Re: Incremental Load from SQL table Simple

Thanks I will try and let you know

shraddha_g
Honored Contributor III

Re: Incremental Load from SQL table Simple

sure