Implementation of Data Pipeline from OLTP to OLAP

 

Project Aim: Building an automated data ingestion pipeline from OLTP to OLAP system

Project Description: A Financial Bank has issued cards to their customers; everyday card transaction take place and through the issued cards which is handled by Online Transaction Processing System by SQL Database server database in background, and some records also arrives in form of Json file format directly HDFS, The Data is collected from these resources and stored in OLAP i.e., Hive Datawarehouse for further Analysis.

Tools used: 1) MySQL,

                    2) Apache Sqoop,

                    3) Apache Hive,

                    4) Apache Airflow

Project Code:

from airflow import DAG

from airflow.utils.dates import days_ago

from airflow.contrib.operators.ssh_operator  import SSHOperator

from airflow.operators.dummy_operator import DummyOperator

from airflow import settings

from airflow.models import Connection

dag = DAG(

    dag_id = 'simple_pipeline',

    schedule_interval = '@daily',

    start_date = days_ago(1)

    )

//[command to create a folder inside a local system]

create_folder_command = f'rm -rf project && mkdir -p project'

//[command to create a folder inside a HDFS system]

create_folder_inside_hadoop_command = f'hdfs dfs -rm -R -f real_project && hdfs dfs -mkdir -p real_project'

//[Command to copy card transaction data in local project folder]

copy_to_folder_command = f'cp -f card_transactions-221229-131756.csv project'

//[Command to copy card transaction data from Project folder to the folder in HDFS file system]

copy_file_to_hadoop_folder = f'hdfs dfs -put project/card_transactions-221229-131756.csv real_project'

//[creating command in airflow to execute the sqoop export shell script to export card_transaction file from hdfs to SQL table]

export_sqoop_command = f'./sqoop_export.sh ms.itversity.com:3306 retail_export retail_user card_transaction_table staging_card_transaction_table'

//[creating command in airflow to execute the sqoop eval shell script to delete existing records]

truncate_sqoop_command = f'./sqoop_eval_trunc.sh ms.itversity.com:3306 retail_export retail_user card_transaction_table'

truncate_table = SSHOperator(

    task_id = 'truncate_data',

    ssh_conn_id = 'itversity',

    command = truncate_sqoop_command,

    dag=dag

    )

export_data_file = SSHOperator(

    task_id = 'export_data',

    ssh_conn_id = 'itversity',

    command = export_sqoop_command,

    dag=dag

    )

create_folder_hadoop = SSHOperator(

task_id = 'create_folder_hadoop',

ssh_conn_id = 'itversity',

command = create_folder_inside_hadoop_command,

dag=dag   

)

download_to_edgenode1 = SSHOperator(

    task_id = 'create_folder',

    ssh_conn_id = 'itversity',

    command = create_folder_command,

    dag=dag

 )

copy_to_folder = SSHOperator(

    task_id = 'copy_file',

    ssh_conn_id = 'itversity',

    command = copy_to_folder_command,

    dag=dag

)

dummy1 = DummyOperator(

    task_id = 'dummy1',

    dag=dag

 )

download_to_edgenode1 >> create_folder_hadoop >> copy_to_folder >> dummy1

//[creating shell script for sqoop eval command to delete existing records command in vi editor]

DBSERVER=${1}

DBNAME=${2}

DBUSER=${3}

DBTABLE=${4}

sqoop eval \

-Dhadoop.security.credential.provider.path=jecks://hdfs/user/itv005018/mysql.dbpassword.jceks \

--connect jdbc:mysql://${DBSERVER}/${DBNAME} \

--username ${DBUSER} \

--password-alias mysql.banking.password \

--query "DELETE FROM ${DBTABLE}" 

//[creating shell script for sqoop export command in vi editor]

[itv005018@g01 ~]$ vi sqoop_export.sh

DBSERVER=${1}

DBNAME=${2}

DBUSER=${3}

DBTABLE=${4}

DBTABLE2=${5}

sqoop export \

-Dhadoop.security.credential.provider.path=jceks://hdfs/user/itv005018/mysql.dbpassword.jceks \

--connect jdbc:mysql://${DBSERVER}/${DBNAME} \

--username ${DBUSER}

--password-alias mysql.banking.password \

--table ${DBTABLE} \

--staging-table ${DBTABLE2} \

--export-dir real_project/card_transactions-221229-131756.csv \

--fields-terminated-by ','

//[Executed Airflow DAG for above commands]

mysql> create table card_transaction

    -> (card_id bigint not null,

    -> member_id bigint not null,

    -> amount int not null,

    -> postcode int not null,

    -> post_id bigint not null,

    -> transaction_dt varchar(255) not null,

    -> txn_category varchar(50)

    -> );

mysql> LOAD DATA INFILE '/home/cloudera/Desktop/shared/card_transactions-221026-145049.csv'

    -> INTO TABLE card_transaction

    -> FIELDS TERMINATED BY ','

    -> ENCLOSED BY '"'

    -> LINES TERMINATED BY '\n';

mysql> create table card_member

    -> (card_id bigint not null,

    -> member_id bigint not null,

    ->  member_joining_dt varchar(255) not null,

    -> card_purchase_dt varchar(255) not null,

    -> country varchar(50),

    -> city varchar(50),

              -> zone varchar(50) 

    -> PRIMARY KEY (member_id)

    -> );

mysql> LOAD DATA INFILE '/home/cloudera/Desktop/shared/cardmembers-210304-135700.csv'

    -> INTO TABLE card_member

    -> FIELDS TERMINATED BY ','

    -> ENCLOSED BY '"'

    -> LINES TERMINATED BY '\n';

mysql> create table member_score

    -> (member_id bigint not null,

    -> score int,

    -> score_category varchar(255),

    -> PRIMARY KEY (member_id)

    -> );

mysql> LOAD DATA INFILE '/home/cloudera/Desktop/shared/memberscore1-210824-132632.csv'

    -> INTO TABLE member_score

    -> FIELDS TERMINATED BY ','

    -> ENCLOSED BY '"'

    -> LINES TERMINATED BY '\n';

---------------------------------------------------------------------------------------------------------------------

[cloudera@quickstart ~]$ hadoop credential create mysql.banking.password -provider jceks://hdfs/user/cloudera/mysql.password.jceks

sqoop job \

> -Dhadoop.security.credential.provider.path=jceks://hdfs/user/cloudera/mysql.password.jceks \

> --create job_members_1 \

> -- import \

> --connect jdbc:mysql://quickstart.cloudera:3306/bank \

> --username root \

> --password-alias mysql.banking.password \

> --table card_member \

> --warehouse-dir /member_data \

> --incremental append \

> --check-column member_id \

> --last-value 0 \

> --compress

sqoop job --exec job_members_1

hadoop fs -ls /member_data/card_member

Found 4 items

-rw-r--r--   1 cloudera cloudera       5608 2022-12-03 23:46 /member_data/card_member/part-m-00000.gz

-rw-r--r--   1 cloudera cloudera       5978 2022-12-03 23:46 /member_data/card_member/part-m-00001.gz

-rw-r--r--   1 cloudera cloudera       5842 2022-12-03 23:46 /member_data/card_member/part-m-00002.gz

-rw-r--r--   1 cloudera cloudera       5647 2022-12-03 23:46 /member_data/card_member/part-m-00003.gz

create table card_member (

    > card_id bigint,

    > member_id bigint,

    > member_joining_dt string,

    > card_purchase_dt string,

    > country string,

    > city string,

    > zone string)

    > row format delimited fields terminated by ',';

hive> load data inpath '/member_data/card_member/*' into table card_member;

hive> select * from card_member limit 10;

OK

348702000000000 37495066290 38:43.0 Feb-18 United States Avon Center North

5189560000000000 117826000000 41:33.0 Apr-15 United States Medina East

5407070000000000 1147920000000 13:06.0 Jun-15 United States Watertown East

378304000000000 1314070000000 01:08.0 Jan-17 United States East Lake-Orient Park South

348413000000000 1739550000000 11:19.0 Jul-17 United States East Independence North

348537000000000 3761430000000 17:03.0 Nov-15 United States Dover North

5515990000000000 4494070000000 21:12.0 Jul-13 United States Estero East

5400250000000000 6836120000000 03:30.0 Sep-17 United States Indian Trail East

4573340000000000 6991870000000 27:14.0 Feb-14 United States Rio Rico South

4708910000000000 7955570000000 24:04.0 Jan-18 United States Fond du Lac South

hive> SET hive.exec.dynamic.partition=true;

hive> SET hive.exec.dynamic.partition.mode = nonstrict;

hive> SET hive.enforce.bucketing=true;

hive> create external table ext_card_member (

    > card_id bigint,

    > member_id bigint,

    > member_joining_dt string,

    > card_purchase_dt string,

    > country string,

    > city string)

    > partitioned by (zone string)

    > clustered by (member_id) into 4 buckets

    > stored as orc;

hive> insert into table ext_card_member partition (zone)

    > select card_id,member_id,member_joining_dt,card_purchase_dt,country,city,zone from card_member;

show partitions ext_card_member;

hive> show partitions ext_card_member;

OK

zone=East

zone=North

zone=South

zone=West

SELECT * FROM ext_card_member TABLESAMPLE(bucket 1 out of 4);

[cloudera@quickstart ~]$ hadoop fs -ls /user/hive/warehouse/ext_card_member/

Found 4 items

drwxrwxrwx   - cloudera supergroup          0 2022-12-04 02:32 /user/hive/warehouse/ext_card_member/zone=East

drwxrwxrwx   - cloudera supergroup          0 2022-12-04 02:32 /user/hive/warehouse/ext_card_member/zone=North

drwxrwxrwx   - cloudera supergroup          0 2022-12-04 02:32 /user/hive/warehouse/ext_card_member/zone=South

drwxrwxrwx   - cloudera supergroup          0 2022-12-04 02:32 /user/hive/warehouse/ext_card_member/zone=West 

 

sqoop job \
> --create job_members \
> -- import \
> --connect jdbc:mysql://quickstart.cloudera:3306/bank \
> --username root \
> --password cloudera \

         > -Dhadoop.security.credential.provider.path=jceks://hdfs/user/cloudera/mysql.password.jceks \ 

> --table card_transaction \

> --warehouse-dir /member_data \

> --split-by card_id \

> --incremental append \

> --check-column card_id \

> --last-value 0 \

> --compress 

sqoop job --exec job_members

         hadoop fs -ls /member_data/card_transaction

Found 4 items
-rw-r--r--   1 cloudera cloudera     305821 2022-11-26 06:48 /member_data/card_transaction/part-m-00000.gz
-rw-r--r--   1 cloudera cloudera         20 2022-11-26 06:48 /member_data/card_transaction/part-m-00001.gz
-rw-r--r--   1 cloudera cloudera     260541 2022-11-26 06:48 /member_data/card_transaction/part-m-00002.gz
-rw-r--r--   1 cloudera cloudera     517629 2022-11-26 06:48 /member_data/card_transaction/part-m-00003.gz

hive> create table card_transaction (
    > card_id bigint,
    > member_id bigint,
    > amount int,
    > postcode int,
    > post_id bigint,
    > transaction_dt string,
    > txn_category string)
    > row format delimited fields terminated by ',';
hive> load data inpath '/member_data/card_transaction/*' into table card_transaction;

hive> select * from card_transaction limit 10;

OK

348702330256514 37495066290 9084849 33946 614677375609919 11-02-2018 00:00 GENUINE
348702330256514 37495066290 330148 33946 614677375609919 11-02-2018 00:00 GENUINE
348702330256514 37495066290 136052 33946 614677375609919 11-02-2018 00:00 GENUINE
348702330256514 37495066290 4310362 33946 614677375609919 11-02-2018 00:00 GENUINE
348702330256514 37495066290 9097094 33946 614677375609919 11-02-2018 00:00 GENUINE
348702330256514 37495066290 2291118 33946 614677375609919 11-02-2018 00:00 GENUINE
348702330256514 37495066290 4900011 33946 614677375609919 11-02-2018 00:00 GENUINE
348702330256514 37495066290 633447 33946 614677375609919 11-02-2018 00:00 GENUINE
348702330256514 37495066290 6259303 33946 614677375609919 11-02-2018 00:00 GENUINE
348702330256514 37495066290 369067 33946 614677375609919 11-02-2018 00:00 GENUINE

hive> SET hive.exec.dynamic.partition=true;
hive> SET hive.exec.dynamic.partition.mode = nonstrict;
hive> SET hive.enforce.bucketing=true;

hive> create external table ext_card_transaction (
    > card_id bigint,
    > member_id bigint,
    > amount int,
    > postcode int,
    > post_id bigint,
    > transaction_dt string)
    > partitioned by (txn_category string)
    > clustered by (card_id) into 4 buckets
    > stored as orc;

hive> insert into table ext_card_transaction partition (txn_category)

    > select card_id,member_id,amount,postcode,post_id,transaction_dt,txn_category from              

        card_transaction;

hive> show partitions ext_card_transaction;

OK

txn_category=FRAUD

txn_category=GENUINE

SELECT * FROM ext_card_transaction TABLESAMPLE(bucket 1 out of 4);

[cloudera@quickstart ~]$ hadoop fs -ls /user/hive/warehouse/ext_card_transaction/

Found 2 items

drwxrwxrwx   - cloudera supergroup          0 2022-11-28 19:06 /user/hive/warehouse/ext_card_transaction/txn_category=FRAUD

drwxrwxrwx   - cloudera supergroup          0 2022-11-28 19:06 /user/hive/warehouse/ext_card_transaction/txn_category=GENUINE

hadoop fs -cat /user/hive/warehouse/ext_card_transaction/txn_category=FRAUD/*

 

 

 

Big Data Project

 

[cloudera@quickstart ~]$ hadoop fs -mkdir /user/cloudera/movies

[cloudera@quickstart ~]$ hadoop fs -mkdir movies/credits

[cloudera@quickstart ~]$ hadoop fs -mkdir movies/titles

[cloudera@quickstart ~]$ hadoop fs -put /home/cloudera/Desktop/shared/credits.csv /user/cloudera/movies/credits

[cloudera@quickstart ~]$ hadoop fs -put /home/cloudera/Desktop/shared/titles.csv /user/cloudera/movies/titles

[cloudera@quickstart ~]$ hadoop fs -cat /user/cloudera/movies/credits/credits.csv | head

[cloudera@quickstart ~]$ hadoop fs -cat /user/cloudera/movies/titles/titles.csv | head

[cloudera@quickstart ~]$ hadoop fs -cat /user/cloudera/movies/credits/credits.csv | wc -l

[cloudera@quickstart ~]$ hadoop fs -cat /user/cloudera/movies/titles/titles.csv | wc -l

mysql> create table if not exists credits

    -> ( person_id int,

    -> id varchar(100),

    -> name varchar(100),

    -> characters varchar(300),

    -> roles varchar(50)

    -> );

create table if not exists titels

    -> (id varchar(100),

    -> title varchar(200),

    -> type varchar(10),

    -> description varchar(2000),

    -> release_year int,

    -> age_certification varchar(10),

    -> runtime int,

    -> genres varchar(500),

    -> production_countries varchar(100),

    -> seasons int,

    -> imdb_id varchar(100),

    -> imdb_score float,

    -> imdb_votes int,

    -> tmdb_popularity float,

    -> tmdb_score float

    -> );

Data Structure

 Find Unique Value in Array:

int main()

{

    int arr[MAX_SIZE], freq[MAX_SIZE];

    int size, i, j, count;

 

    /* Input size of array and elements in array */

    printf("Enter size of array: ");

    scanf("%d", &size);

    printf("Enter elements in array: ");

    for(i=0; i<size; i++)

    {

        scanf("%d", &arr[i]);

        freq[i] = -1;

        printf("print value of freq[i] after scanf ");  /* understanding what values are taken into freq[i]*/

        printf("%d",freq[i]); /* printing values of freq[i]*/

    }

    

    /* Find frequency of each element */

    for(i=0; i<size; i++)

    {

        printf("\nprint value of arr[i] ");

        printf("%d", arr[i]); /* printing value of arr[i] to understand what value it fetches on every loop*/

        count = 1; 

        for(j=i+1; j<size; j++)

        {

            if(arr[i] == arr[j])

            {

                count++;

                freq[j] = 0;

                printf("\nValue of Count ");

                printf("%d",count);

                printf("\nValue of freq[j] "); /* Printing Values of Freq[j] to understand what value it stores */

                printf("%d",freq[j]);

            }

        }

        printf("\nprint value of freq[i] ");

        printf("%d",freq[i]);

        if(freq[i] != 0)

        {

            freq[i] = count;

            printf("\nValue of freq[i] ");

            printf("%d", freq[i]);

        }

    }

 

    /* Print all unique elements of array */

    printf("\nUnique elements in the array are: ");

    for(i=0; i<size; i++)

    {

        if(freq[i] == 1)

        {

            printf("%d ", arr[i]);

        }

    }

    return 0;

}

SQL Project

 SQL Project Code at Suryoday Small Finance Bank

---#19816 Rera Account EOD Balance

select d.boo
      ,[desc]
      ,d.title1
      ,d.cid
      ,d.bal
      ,d.as_on_dt
from profile.dbo.deposit d join profile.dbo.brnch_master b
on d.boo = b.brcd and 
   b.AS_ON_DTE = convert(date,getdate()-1)
where d.type = 2010 and 
not(PGK = 1 and BAL = 0) and 
STAT <> 4 and 
d.AS_ON_DT between dateadd(day,1,EOMONTH(getdate()-1,-1)) and convert(date,(getdate()-1));

----To monitor credit transactions in Rera Master Collection Account

use PROFILE

declare @tdate date = convert(date,getdate()-2)
SELECT t.AS_ON_DTE AS_ON
       ,ROW_NUMBER() over (order by t.cid) [Sr.No]
       ,tied [Account No]
	   ,sum(t.TAMT) Total_Credit_Bal
	  ,0.7*sum(t.TAMT) [70% of Credit Transaction]
	  ,0.3*sum(t.TAMT) [30% of Credit Transaction]
from	dbo.TXN_HIST t
		join dbo.DEPOSIT d
		on t.CID = d.CID
where
		d.TYPE = 2010
		and not(d.PGK = 1 and BAL = 0) and d.STAT <> 4
		and t.AS_ON_DTE = @tdate
		and d.AS_ON_DT = @tdate and DRCR = 1
group by 
		T.AS_ON_DTE,t.CID


------To Query GL Balance Group Loan Balance

select sum([GL Balance])
from
(
select g.CC [Branch Code]
     , b.[DESC] [Branch Name]
     , b.STATENAM [State Name],'         ' "CRL Limit"
     ,g.BAL [GL Balance]
from GL_BALANCE g join BRNCH_MASTER b on g.CC = b.BRCD
where ACN = '301010060001'
and g.AS_ON_DTE = convert(varchar,getdate()-1,106) 
and b.AS_ON_DTE = convert(varchar,getdate()-1,106)
order by [Branch Name] ) t


----Cash Retention Limit report - URC


select * from
(select g.cc
      ,b.[DESC] [Branch_Name]
      , G.ACN,isnull(g.bal,0) [glbal]
from GL_BALANCE g join BRNCH_MASTER b on g.CC = b.BRCD
and g.AS_ON_DTE = '20 Oct 2020' 
and b.AS_ON_DTE = '20 Oct 2020') t
pivot ( sum(glbal)
for acn in ([300110010001],[300410010001],[300410020001],[301010060001],[301010010001],[301010020001],[301010030001],[301010040001],[301010050001],[301110010001],[301210010001],[301020010001],[301410010001] )) pivottable

----Count of DD issued

select c.DBRANCH [Home Branch Code]
      ,c.PAYAT [Transaction done at Branch Code]
      ,c.PAYDATE
      ,c.SERNO [DD Serial No.]
      ,c.AMOUNT
      ,c.PAYEENAME [Payee Name]
      ,c.PURCHACN
from CASH_DDPO c
join (
select DBRANCH
      ,count(remtype) cremtype
from CASH_DDPO 
where STATUS = 'paid'
group by DBRANCH
having count(remtype) >= 50) t
on c.DBRANCH = t.DBRANCH
where c.STATUS = 'paid'


----Withdrawal Transaction Lien Marked Acnt


select h.cid
      ,h.tamt
      ,h.tcmt
      ,h.DRCR
      ,h.ETC
from TXN_HIST h
join DEPOSIT d on h.CID = d.CID
--select d.CID,d.hldamt from DEPOSIT d
where 
d.HLDAMT > 0
and h.DRCR = 0
and d.TYPE between 3000 and 4000
and d.AS_ON_DT = '1 Nov 2020'
--and h.AS_ON_DTE = '1 Nov 2020'
and d.BAL > 0
and not(d.PGK = 1 and BAL = 0) and d.STAT <> 4 and h.TCMT <> 'WithHold(TDS)' and h.TCMT <> 'Interest/Dividend Available Not Credited'
and ETC <> 'ITOC'


----transaction freeze query

select d.BOO [Home Branch Code],h.brcd [Transaction Done at Branch Code],h.CID [Account Number],t.[DESC],h.TAMT [Amount],h.[TJD DATE] [Transaction Date],
h.tim [Time of Transaction],case when DRCR = 0 then 'DR' else 'CR' END as [Transaction Type Desc],
case when TYPE between 1000 and 1999 then 'Savings A/c' 
     when TYPE between 2000 and 2999 then 'Current A/c'
     when TYPE between 3000 and 3999 then 'Fixed A/c'
     when TYPE between 4000 and 5000 then 'Recurring A/c'
	  end as [Account Type],
 d.TITLE1 [Account Name],h.USERID [Input Done By],
 case when h.TSO like '%MCCKRID%' then  SUBSTRING((SUBSTRING(h.TSO,CHARINDEX('MCCKRID',h.TSO),13)),9,13)
      when h.TSO not like '%MCCKRID%' then '' 
 end as [Authorization done]
 from TXN_HIST h
join
(
select r.CID, r.rflg,r.[DESC], ROW_NUMBER() over (order by CID) as [row_no] from ACCT_LEVEL_RESTRICTION r
--where r.[DESC] like '%Restricts Debit%'
WHERE r.RFLG in ('001','002','003','004','006','011','023','R001') and AS_ON_DTE = convert(date,getdate()-1)) t
on h.CID = t.CID
and t.row_no = 1
join DEPOSIT d
on h.CID = d.CID
join Suryoday.dbo.ETC_TxnList etc
on h.ETC = etc.ETC
where h.AS_ON_DTE = convert(date,getdate()-1)
and d.AS_ON_DT = convert(date,getdate()-1)
and etc.FeesCharges in ('',NULL) and len(etc.FeesCharges) = 0
and COMPONENT in ('',NULL) and len(COMPONENT)=0
and h.ETC not in ('ITID','ITIS','INIS','IIAS','IIPS','INID')
and h.DRCR <> 1
and not(d.PGK = 1 and BAL = 0) and d.STAT <> 4
UNION ALL
select d.BOO [Home Branch Code],h.brcd [Transaction Done at Branch Code],h.CID [Account Number],t.[DESC] [Description],h.TAMT [Amount],h.[TJD DATE] [Transaction Date],
h.tim [Time of Transaction],case when DRCR = 0 then 'DR' else 'CR' END as [Transaction Type Desc],
case when TYPE between 1000 and 1999 then 'Savings A/c' 
      when TYPE between 2000 and 2999 then 'Current A/c'
	  when TYPE between 3000 and 3999 then 'Fixed A/c'
	  when TYPE between 4000 and 5000 then 'Recurring A/c'
	  end as [Account Type],
 d.TITLE1 [Account Name],h.USERID [Input Done By],
 case when h.TSO like '%MCCKRID%' then  substring((SUBSTRING(h.TSO,CHARINDEX('MCCKRID',h.TSO),13)),9,13)
      when h.TSO not like '%MCCKRID%' then '' 
 end as [Authorization done]
 from TXN_HIST h
join
(
select r.CID, r.rflg,r.[DESC], ROW_NUMBER() over (order by CID) as row_no from ACCT_LEVEL_RESTRICTION r
--where r.[DESC] like '%Restricts Debit%'
WHERE r.RFLG in ('008','009') and AS_ON_DTE = CONVERT(date,GETDATE()-1)) t
on h.CID = t.CID
and t.row_no = 1
join DEPOSIT d
on h.CID = d.CID
join Suryoday.dbo.ETC_TxnList etc
on h.ETC = etc.ETC
where h.AS_ON_DTE = convert(date,getdate()-1)
and d.AS_ON_DT = convert(date,getdate()-1)
and etc.FeesCharges in ('',NULL) and len(etc.FeesCharges) = 0
and COMPONENT in ('',NULL) and len(COMPONENT)=0
and h.ETC not in ('ITID','ITIS','INIS','IIAS','IIPS','INID')
and not(d.PGK = 1 and BAL = 0) and d.STAT <> 4


-----#31018 Video KYC Account Opening_updated

use PROFILE
declare @tdate date = convert(date,getdate()-1)
select 
	 d.CID Account_No
	,c.ACN Customer_Id
        ,d.BOO BranchCode
	,b.[DESC] [BranchName]
	,c.FNAME First_Name
	,c.LNM Last_Name
        ,c.ZMPH Mobile_No
	,c.EMAIL Email_Id
	,c.oit Identification_Type
	,format(d.ODT,'dd-MMM-yyyy') Account_Opening_Date
	,p.[DES] [Variant of Account Opened]
	,format(d.ZACTDAT1, 'dd-MMM-yyyy') Account_Activation_Date
from dbo.CUSTOMER_MASTER c
join dbo.DEPOSIT d
on c.ACN = d.ACN
join dbo.PRODCTL p
on d.TYPE = p.TYPE
join UTBLBRCD b
on b.BRCD = d.BOO
where 
d.AS_ON_DT = @tdate
and c.CIF_STAT = 1 and d.ACCT_OPN_DTE = @tdate
--and c.acn <> '140000001'
and c.OIT in (31,36)
and not(d.PGK = 1 and d.BAL = 0) and d.STAT <> 4 and d.[TYPE] < 2000
--and d.type in ('1008','1005','2003','2001','1004','2006','1003')

------FD not closed but Fund got transferred


select d.CID,d.MDT,d.BAL
from DEPOSIT d
join TXN_HIST t
on d.CID = t.CID
where 
d.mdt <> ''
and convert(date,convert(date,d.MDT,105),23) < convert(date,getdate(),103)
and d.TYPE between 3000 and 4000
and d.STAT <> 4
and d.pgk = '0'
and d.BAL <= 0
and d.AS_ON_DT = convert(varchar,GETDATE()-1,106)
--and d.AS_ON_DT < convert(varchar,GETDATE()-1,106) and t.AS_ON_DTE < convert(varchar,GETDATE()-1,106)

VBA Project Code-Merchandising Report

Case Study: VLookup data from Multiple Workbooks, and Apply Calculations in Master Workbook to update the Master File

Number Of Files: 4

Recon_Rough(Master File)

1) Merchandise Fullfilment Report

2) Merchandise Redemption Report

3) Orders Export File

Description:

Update Recon_Rough(Master File) from 

1)Merchandise Fullfilment Report.xlsx

2) Merchandise Redemption Report.xlsx

3)Orders Export File

Sub Merchandise_12_09_2019()

Application.ScreenUpdating = False

Dim Recon_Template As Workbook

Dim wbk As Workbook

Dim x As Variant

Dim lrow As Integer

lrow = Cells(Rows.Count, 1).End(xlUp).Row

Set wbk = Workbooks.Open("D:\Recong\Merchandise_Fulfillment_12-09-2019.xlsx")

Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _

Range("Q2:Q" & lrow).Formula = "=VLOOKUP($D2,'D:\Recong\[" & "Merchandise_Fulfillment_12-09-2019.xlsx" & "]" & "Combined" & "'!$B:$K,10,0)"

Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _

Range("R2:R" & lrow).Formula = "=VLOOKUP($D2,'D:\Recong\[" & "Merchandise_Fulfillment_12-09-2019.xlsx" & "]" & "Combined" & "'!$B:$X,23,0)"

ActiveSheet.Range("R:R").Select

Selection.NumberFormat = "dd-mmm-yy"

Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _

Range("S2:S" & lrow).Formula = "=VLOOKUP($D2,'D:\Recong\[" & "Merchandise_Fulfillment_12-09-2019.xlsx" & "]" & "Combined" & "'!$B:$AC,28,0)"

Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _

Range("T2:T" & lrow).Formula = "=VLOOKUP($D2,'D:\Recong\[" & "Merchandise_Fulfillment_12-09-2019.xlsx" & "]" & "Combined" & "'!$B:$AI,34,0)"

Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _

Range("U2:U" & lrow).Formula = "=VLOOKUP($D2,'D:\Recong\[" & "Merchandise_Fulfillment_12-09-2019.xlsx" & "]" & "Combined" & "'!$B:$N,13,0)"

Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _

Range("V2:V" & lrow).Formula = "=VLOOKUP($D2,'D:\Recong\[" & "Merchandise_Fulfillment_12-09-2019.xlsx" & "]" & "Combined" & "'!$B:$R,17,0)"

Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _

Range("W2:W" & lrow).Formula = "=VLOOKUP($D2,'D:\Recong\[" & "Merchandise_Fulfillment_12-09-2019.xlsx" & "]" & "Combined" & "'!$B:$S,18,0)"

Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _

Range("X2:X" & lrow).Formula = "=$U2*$V2"

Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _

Range("Z2:Z" & lrow).Formula = "=VLOOKUP($D2,'D:\Recong\[" & "Merchandise_Fulfillment_12-09-2019.xlsx" & "]" & "Combined" & "'!$B:$W,22,0)"

Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _

Range("AA2:AA" & lrow).Formula = "=VLOOKUP($D2,'D:\Recong\[" & "Merchandise_Fulfillment_12-09-2019.xlsx" & "]" & "Combined" & "'!$B:$U,20,0)"

Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _

Range("AB2:AB" & lrow).Formula = "=VLOOKUP($D2,'D:\Recong\[" & "Merchandise_Fulfillment_12-09-2019.xlsx" & "]" & "Combined" & "'!$B:$Q,16,0)"

Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _

Range("AC2:AC" & lrow).Formula = "=VLOOKUP($D2,'D:\Recong\[" & "Merchandise_Fulfillment_12-09-2019.xlsx" & "]" & "Combined" & "'!$B:$T,19,0)"

Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _

Range("AD2:AD" & lrow).Formula = "=$AB2*0.25"

Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _

Range("AE2:AE" & lrow).Formula = "=$AC2-$AD2"

Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _

Range("AF2:AF" & lrow).Formula = "=$AA2+$AD2"

Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _

Range("AG2:AG" & lrow).Formula = "=$AF2-$Z2"

Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _

Range("AH2:AH" & lrow).Formula = "=AND(AF2>=X2,Y2=Z2)"

Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _

Range("AI2:AI" & lrow).Formula = "=Y2>X2"

Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _

Range("AM2:AM" & lrow).Formula = "=VLOOKUP($D2,'D:\Recong\[" & "Merchandise_Fulfillment_12-09-2019.xlsx" & "]" & "Combined" & "'!$B:$AR,43,0)"

Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1").Cells.Copy

Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1").Cells.PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False

Workbooks("Merchandise_Fulfillment_12-09-2019.xlsx").Close SaveChanges:=False

Application.ScreenUpdating = True

Call Merchandise_Redemption

End Sub

-------------------------------------------------------------

Sub Merchandise_Redemption()

Application.ScreenUpdating = False

Dim wbk As Workbook

Dim lrow1 As Long

Dim Rng1 As Range

lrow1 = Cells(Rows.Count, 1).End(xlUp).Row - 1

Set wbk = Workbooks.Open("D:\Recong\Merchandise_Redemption_Report.xlsx")

Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1").Activate

Worksheets("Sheet1").Range("A1").AutoFilter Field:=17, Criteria1:="#N/A", Operator:=xlOr, Criteria2:="="

With ActiveSheet.AutoFilter.Range

Set Rng1 = Range("R" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row)

.Range(Rng1.Address & ":R" & lrow1).Formula = "=VLOOKUP($D:$D,'D:\Recong\[" & "Merchandise_Redemption_Report.xlsx" & "]" & "Merchandise_Redemption_Report" & "'!$A:$AF,8,0)"

.Range(Rng1.Address & ":R" & lrow1).Offset(0, 2).Formula = "=VLOOKUP($D:$D,'D:\Recong\[" & "Merchandise_Redemption_Report.xlsx" & "]" & "Merchandise_Redemption_Report" & "'!$A:$AF,17,0)"

.Range(Rng1.Address & ":R" & lrow1).Offset(0, 3).Formula = "=VLOOKUP($D:$D,'D:\Recong\[" & "Merchandise_Redemption_Report.xlsx" & "]" & "Merchandise_Redemption_Report" & "'!$A:$AF,19,0)"

.Range(Rng1.Address & ":R" & lrow1).Offset(0, 4).Formula = "=VLOOKUP($D:$D,'D:\Recong\[" & "Merchandise_Redemption_Report.xlsx" & "]" & "Merchandise_Redemption_Report" & "'!$A:$AF,20,0)"

.Range(Rng1.Address & ":R" & lrow1).Offset(0, 5).Formula = "=SUMPRODUCT(VLOOKUP($D:$D,'D:\Recong\[" & "Merchandise_Redemption_Report.xlsx" & "]" & "Merchandise_Redemption_Report" & "'!$A:$AF,{21,22},0))"

.Range(Rng1.Address & ":R" & lrow1).Offset(0, 7).Formula = "=VLOOKUP($D:$D,'D:\Recong\[" & "Merchandise_Redemption_Report.xlsx" & "]" & "Merchandise_Redemption_Report" & "'!$A:$AF,28,0)"

.Range(Rng1.Address & ":R" & lrow1).Offset(0, 8).Formula = "=VLOOKUP($D:$D,'D:\Recong\[" & "Merchandise_Redemption_Report.xlsx" & "]" & "Merchandise_Redemption_Report" & "'!$A:$AF,28,0)"

.Range(Rng1.Address & ":R" & lrow1).Offset(0, 9).Formula = "=VLOOKUP($D:$D,'D:\Recong\[" & "Merchandise_Redemption_Report.xlsx" & "]" & "Merchandise_Redemption_Report" & "'!$A:$AF,15,0)"

.Range(Rng1.Address & ":R" & lrow1).Offset(0, 10).Formula = "=VLOOKUP($D:$D,'D:\Recong\[" & "Merchandise_Redemption_Report.xlsx" & "]" & "Merchandise_Redemption_Report" & "'!$A:$AF,14,0)"

.Range(Rng1.Address & ":R" & lrow1).Offset(0, 12).Formula = "=$AB:$AB * 0.25"

.Range(Rng1.Address & ":R" & lrow1).Offset(0, 13).Formula = "=$AC:$AC-$AD:$AD"

.Range(Rng1.Address & ":R" & lrow1).Offset(0, 14).Formula = "=$AA:$AA+$AD:$AD"

.Range(Rng1.Address & ":R" & lrow1).Offset(0, 15).Formula = "=$AF:$AF-$Z:$Z"

.Range(Rng1.Address & ":R" & lrow1).Offset(0, 16).Formula = "=AND($AF:$AF>=$X:$X,$Y:$Y=$Z:$Z)"

.Range(Rng1.Address & ":R" & lrow1).Offset(0, 17).Formula = "=$Y:$Y>$Z:$Z"

.Range(Rng1.Address & ":R" & lrow1).Offset(0, 21).Formula = "=VLOOKUP($D:$D,'D:\Recong\[" & "Merchandise_Redemption_Report.xlsx" & "]" & "Merchandise_Redemption_Report" & "'!$A:$AF,10,0)"

End With

Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1").ShowAllData

Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1").Cells.Copy

Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1").Cells.PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False

Workbooks("Merchandise_Redemption_Report.xlsx").Close SaveChanges:=False

Application.ScreenUpdating = True

Call Recon_Template2

End Sub

--------------------------------------------------------------------------------------------------------------------------

Application.ScreenUpdating = False

Dim wbk As Workbook

Dim lrow2 As Long

Dim Rng1 As Range

lrow2 = Cells(Rows.Count, 1).End(xlUp).Row

Set wbk = Workbooks.Open("D:\Recong\orders_export_13_9_2019_12_0.xlsx")

Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1").Activate

Worksheets("Sheet1").Range("A1").AutoFilter Field:=17, Criteria1:="#N/A", Operator:=xlOr, Criteria2:="="

With ActiveSheet.AutoFilter.Range

Set Rng1 = Range("Q" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row)

.Range(Rng1.Address & ":Q" & lrow2).Formula = "=VLOOKUP($D:$D,'D:\Recong\[" & "orders_export_13_9_2019_12_0" & "]" & "data" & "'!$F:$AV,18,0)"

.Range(Rng1.Address & ":Q" & lrow2).Offset(0, 2).Formula = "=VLOOKUP($D:$D,'D:\Recong\[" & "orders_export_13_9_2019_12_0" & "]" & "data" & "'!$F:$AV,37,0)"

End With

Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1").ShowAllData

Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1").Cells.Copy

Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1").Cells.PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False

Workbooks("orders_export_13_9_2019_12_0.xlsx").Close SaveChanges:=False

Application.ScreenUpdating = True