Skip to Content

This hands-on guide explains how to use “Data Manager” to enrich your data semantically with additional attributes to produce stronger predictive models.

 

Based around transactional data from a Bank, you identify customers likely to sign up for a credit card in the following quarter. In a number of iterations you enhance the dataset and improve the model gradually.

 

Please note that this guide is giving a high-level introductory overview and only shows a small fraction of the available functionality.

 

Download from GitHub

  • Tutorial
  • Data (for SAP HANA)
  • Data (for SQL Server) Time stamps have been updated in this SQL Server data. Please use 2016-08-05 as reference when prompted in Data Manager.

 

Happy Predicting!

 

Updates:

1. February 2016: Added .mdf file for SQL Server as alternative data source for the tutorial.

 

camera.PNG

To report this post you need to login first.

17 Comments

You must be Logged on to comment or reply to a post.

  1. Venkata Ramana Paidi

    Hi Andreas,

    The document is very good.

    In the document result we are loading to the table “MyScores”. Could you please provide the DDL script for the “MyScores”.

    Thanks & Regards,

    Venkata Ramana Paidi

    (0) 
  2. Andreas Forster Post author

    Hello Venkata, nice to hear you find the tutorial helpful!

    The “MyScores” table is created on the fly when saving the scores into SAP HANA. The table does not exist when starting the analysis, so I dont have any DDL script. But when you have completed the tutorial, the table will be there. Would this work for you?

    (0) 
    1. Venkata Ramana Paidi

      Hi Andreas,

      I gave the settings as below in the apply the Model options

      After run the model , I am getting the below errors:

      2015-11-22 09:51:26    Error    Message from ODBC: SQLExecDirect: [42S02][SAP AG][LIBODBCHDB DLL][HDBODBC] Base table or view not found;259 invalid table name:  Could not find table/view KX_1448171484232536TSP_BUYSCREDITCARDNEXTQUARTER in schema VENKAT: line 1 col 1170 (at pos 1169)

      2015-11-22 09:51:26    Error    Failure when creating an object in script KX_1448171485234536_FROMMODEL____ANA_CUSTOMER__TSP_BUYSCREDITCARDNEXTQUARTER_1448171402.

      2015-11-22 09:51:25    Warning    The optimized in-database application process has failed.

      The standard application process will be used instead.

      2015-11-22 09:51:24    Error    The dataset MyScores does not exist.

      2015-11-22 09:51:24    Error   

      2015-11-22 09:51:23    Error    Message from ODBC: insert into KX_144817147520853645CDABB0 (select KXTEMPT1.KXID as KXID, to_timestamp(‘2014-11-05 12:00:00’) as KXTIMESTAMP, KXTEMPT1.CLIENT_ID as CLIENT_ID, KXTEMPT1.DISTRICT_ID as DISTRICT_ID, KXTEMPT1.A5 as A5, KXTEMPT1.A7 as A7, KXTEMPT1.A11 as A11, KXTEMPT1.A12 as A12, KXTEMPT1.A14 as A14, KXTEMPT1.A15 as A15, KXTEMPT1.A16 as A16, KXTEMPT1.DISP_ID as DISP_ID, KXTEMPT1.ACCOUNT_ID as ACCOUNT_ID, KXTEMPT1.TYPE as TYPE, KXTEMPT1.DISTRICT_ID_2 as DISTRICT_ID_2, (days_between(KXTEMPT1.DATE_BIRTH , to_timestamp(‘2014-11-05 12:00:00’)) / 3.6525e2) as AGE, CORRT2.Expr_22 as QUARTERLYTRANSACTION_4Q4B_OPERATION_WITHDRAWALINCASH_SUM_0_AMOUNT, CORRT1.Expr_28 as QUARTERLYTRANSACTION_4Q4B_OPERATION_CREDITINCASH_SUM_1_AMOUNT, KXTEMPT1.BUYSCREDITCARDINQUARTERAFTERTIMESTAMP_1Q100A_EXST_0_CARD_ID as BUYSCREDITCARDINQUARTERAFTERTIMESTAMP_1Q100A_EXST_0_CARD_ID from KX_1448171476211536_FROMMODEL____ANA_CUSTOMER__TSP_BUYSCREDITCARDNEXTQUARTER_1448171402 KXTEMPT1 left outer join (select row_number() over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0 order by I056450TRANSACTION.”DATE” asc) as rn,  KXTEMPT1.REFT3_KXRN0 as REFT3_KXRN0, I056450TRANSACTION.ACCOUNT_ID as JKey, sum(I056450TRANSACTION.AMOUNT) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_28 from KX_1448171476211536_FROMMODEL____ANA_CUSTOMER__TSP_BUYSCREDITCARDNEXTQUARTER_1448171402 KXTEMPT1,  I056450.”TRANSACTION”  I056450TRANSACTION where ((I056450TRANSACTION.ACCOUNT_ID = KXTEMPT1.ACCOUNT_ID) and ((I056450TRANSACTION.”DATE” >= add_months(to_timestamp(‘2014-11-05 12:00:00’) , -9)) and (I056450TRANSACTION.”DATE” < add_months(to_timestamp(‘2014-11-05 12:00:00′) , -6)) and I056450TRANSACTION.OPERATION in (N’CREDIT IN CASH’)))) CORRT1 on ((CORRT1.JKey = KXTEMPT1.ACCOUNT_ID) and ( KXTEMPT1.REFT3_KXRN0 = CORRT1.REFT3_KXRN0) and (CORRT1.rn = 1)) left outer join (select row_number() over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0 order by I056450TRANSACTION.”DATE” asc) as rn,  KXTEMPT1.REFT3_KXRN0 as REFT3_KXRN0, I056450TRANSACTION.ACCOUNT_ID as JKey, sum(I056450TRANSACTION.AMOUNT) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_22 from KX_1448171476211536_FROMMODEL____ANA_CUSTOMER__TSP_BUYSCREDITCARDNEXTQUARTER_1448171402 KXTEMPT1,  I056450.”TRANSACTION”  I056450TRANSACTION where ((I056450TRANSACTION.ACCOUNT_ID = KXTEMPT1.ACCOUNT_ID) and ((I056450TRANSACTION.”DATE” >= add_months(to_timestamp(‘2014-11-05 12:00:00’) , -12)) and (I056450TRANSACTION.”DATE” < add_months(to_timestamp(‘2014-11-05 12:00:00′) , -9)) and I056450TRANSACTION.OPERATION in (N’WITHDRAWAL IN CASH’)))) CORRT2 on ((CORRT2.JKey = KXTEMPT1.ACCOUNT_ID) and ( KXTEMPT1.REFT3_KXRN0 = CORRT2.REFT3_KXRN0) and (CORRT2.rn = 1))): [S1000][SAP AG][LIBODBCHDB DLL][HDBODBC] General error;287 cannot insert NULL or update to NULL: TrexUpdate failed on table ‘VENKAT:KX_144817147520853645CDABB0’ with error: constraint NOT NULL violation;checkNotNullColumn(): failed on pos 0,column ‘DISTRICT_ID_2’, table ‘VENKAT:KX_144817147520853645CDABB0’, rc=56

      2015-11-22 09:51:22    Information    The data manipulation will be materialized into ”KX_144817147520853645CDABB0”.

      Please find the below log file information:



      Checking internal state.

      Requesting the number of cases of <SQLScript><IntermediateEntities><Entity><Type>TABLE</Type><Name>KX_144817069696536TSP_BUYSCREDITCARDNEXTQUARTER</Name></Entity><Entity><Type>TABLE</Type><Name>KX_144817069798536TSP_BUYSCREDITCARDNEXTQUARTER</Name></Entity><Entity><Type>TABLE</Type><Name>KX_144817069694536ANA_CUSTOMER_TSP_BUYSCREDITCARDNEXTQUARTER_1448170617</Name></Entity></IntermediateEntities><CleanSQL><SQL>DROP TABLE KX_144817069696536TSP_BUYSCREDITCARDNEXTQUARTER</SQL><SQL>DROP TABLE KX_144817069798536TSP_BUYSCREDITCARDNEXTQUARTER</SQL><SQL>DROP TABLE KX_144817069694536ANA_CUSTOMER_TSP_BUYSCREDITCARDNEXTQUARTER_1448170617</SQL></CleanSQL><CreateSQL><SQL>CREATE COLUMN TABLE KX_144817069696536TSP_BUYSCREDITCARDNEXTQUARTER (KXID BIGINT,DISP_ID BIGINT,  PRIMARY KEY (KXID))</SQL><SQL>INSERT INTO KX_144817069696536TSP_BUYSCREDITCARDNEXTQUARTER select ENT_CUSTOMER.KXID as KXID, I056450DISPOSITION.DISP_ID as DISP_ID from (select I056450CLIENT.CLIENT_ID as KXID from I056450.CLIENT I056450CLIENT)  ENT_CUSTOMER left outer join I056450.DISPOSITION I056450DISPOSITION on (ENT_CUSTOMER.KXID = I056450DISPOSITION.CLIENT_ID)</SQL><SQL>CREATE COLUMN TABLE KX_144817069798536TSP_BUYSCREDITCARDNEXTQUARTER (KXID BIGINT,KXTIMESTAMP TIMESTAMP,BUYSCREDITCARDINQUARTERAFTERTIMESTAMP_1Q100A_EXST_0_CARD_ID BIGINT,  PRIMARY KEY (KXID,KXTIMESTAMP))</SQL><SQL>INSERT INTO KX_144817069798536TSP_BUYSCREDITCARDNEXTQUARTER select KXTEMPT1.KXID as KXID, to_timestamp(‘2014-08-05 16:57:18’) as KXTIMESTAMP, case when ((exists (select ‘1’ from I056450.CARD  I056450CARD where ((I056450CARD.DISP_ID = KXTEMPT1.DISP_ID) and ((I056450CARD.ISSUED >= to_timestamp(‘2014-08-05 16:57:18’)) and (I056450CARD.ISSUED < add_months(to_timestamp(‘2014-08-05 16:57:18’) , 3))))))) then 1 else 0 end  as BUYSCREDITCARDINQUARTERAFTERTIMESTAMP_1Q100A_EXST_0_CARD_ID from KX_144817069696536TSP_BUYSCREDITCARDNEXTQUARTER KXTEMPT1 where (case when ((not exists (select ‘1’ from I056450.CARD  I056450CARD where ((I056450CARD.DISP_ID = KXTEMPT1.DISP_ID) and ((I056450CARD.ISSUED >= add_years(to_timestamp(‘2014-08-05 16:57:18’) , -100)) and (I056450CARD.ISSUED < to_timestamp(‘2014-08-05 16:57:18’))))))) then 1 else 0 end  = 1)</SQL><SQL>CREATE COLUMN TABLE KX_144817069694536ANA_CUSTOMER_TSP_BUYSCREDITCARDNEXTQUARTER_1448170617 (KXID BIGINT,KXTIMESTAMP TIMESTAMP,CLIENT_ID BIGINT,DISTRICT_ID BIGINT,GENDER NVARCHAR(255),DATE_BIRTH DATE,A2 NVARCHAR(255),A3 NVARCHAR(255),A4 BIGINT,A5 BIGINT,A6 BIGINT,A7 BIGINT,A8 BIGINT,A9 BIGINT,A10 DOUBLE,A11 BIGINT,A12 NVARCHAR(255),A13 DOUBLE,A14 BIGINT,A15 NVARCHAR(255),A16 BIGINT,DISP_ID BIGINT,ACCOUNT_ID BIGINT,TYPE NVARCHAR(255),DISTRICT_ID_2 BIGINT,FREQUENCY NVARCHAR(255),”DATE” DATE,BUYSCREDITCARDINQUARTERAFTERTIMESTAMP_1Q100A_EXST_0_CARD_ID BIGINT,REFT3_KXRN0 BIGINT,  PRIMARY KEY (KXID,KXTIMESTAMP,CLIENT_ID))</SQL><SQL>INSERT INTO KX_144817069694536ANA_CUSTOMER_TSP_BUYSCREDITCARDNEXTQUARTER_1448170617 select ENT_CUSTOMERPOPULATION.KXID as KXID, to_timestamp(‘2014-08-05 16:57:18’) as KXTIMESTAMP, I056450CLIENT.CLIENT_ID as CLIENT_ID, I056450CLIENT.DISTRICT_ID as DISTRICT_ID, I056450CLIENT.GENDER as GENDER, I056450CLIENT.DATE_BIRTH as DATE_BIRTH, I056450DISTRICT.A2 as A2, I056450DISTRICT.A3 as A3, I056450DISTRICT.A4 as A4, I056450DISTRICT.A5 as A5, I056450DISTRICT.A6 as A6, I056450DISTRICT.A7 as A7, I056450DISTRICT.A8 as A8, I056450DISTRICT.A9 as A9, I056450DISTRICT.A10 as A10, I056450DISTRICT.A11 as A11, I056450DISTRICT.A12 as A12, I056450DISTRICT.A13 as A13, I056450DISTRICT.A14 as A14, I056450DISTRICT.A15 as A15, I056450DISTRICT.A16 as A16, I056450DISPOSITION.DISP_ID as DISP_ID, I056450DISPOSITION.ACCOUNT_ID as ACCOUNT_ID, I056450DISPOSITION.TYPE as TYPE, I056450ACCOUNT.DISTRICT_ID as DISTRICT_ID_2, I056450ACCOUNT.FREQUENCY as FREQUENCY, I056450ACCOUNT.”DATE” as “DATE”, ENT_CUSTOMERPOPULATION.BUYSCREDITCARDINQUARTERAFTERTIMESTAMP_1Q100A_EXST_0_CARD_ID as BUYSCREDITCARDINQUARTERAFTERTIMESTAMP_1Q100A_EXST_0_CARD_ID, I056450DISPOSITION.REFT3_KXRN0 from KX_144817069798536TSP_BUYSCREDITCARDNEXTQUARTER  ENT_CUSTOMERPOPULATION left outer join I056450.CLIENT I056450CLIENT on (ENT_CUSTOMERPOPULATION.KXID = I056450CLIENT.CLIENT_ID) left outer join I056450.DISTRICT I056450DISTRICT on (I056450CLIENT.DISTRICT_ID = I056450DISTRICT.DISTRICT_ID) left outer join (select REFT.*, row_number() over (partition by REFT.ACCOUNT_ID order by REFT.ACCOUNT_ID asc) as REFT3_KXRN0 from I056450.DISPOSITION REFT) I056450DISPOSITION on (ENT_CUSTOMERPOPULATION.KXID = I056450DISPOSITION.CLIENT_ID) left outer join I056450.ACCOUNT I056450ACCOUNT on (ENT_CUSTOMERPOPULATION.KXID = I056450ACCOUNT.ACCOUNT_ID)</SQL></CreateSQL><SelectSQL><SQL>select KXTEMPT1.KXID as KXID, to_timestamp(‘2014-08-05 16:57:18’) as KXTIMESTAMP, KXTEMPT1.CLIENT_ID as CLIENT_ID, KXTEMPT1.DISTRICT_ID as DISTRICT_ID, KXTEMPT1.GENDER as GENDER, KXTEMPT1.A2 as A2, KXTEMPT1.A3 as A3, KXTEMPT1.A4 as A4, KXTEMPT1.A5 as A5, KXTEMPT1.A6 as A6, KXTEMPT1.A7 as A7, KXTEMPT1.A8 as A8, KXTEMPT1.A9 as A9, KXTEMPT1.A10 as A10, KXTEMPT1.A11 as A11, KXTEMPT1.A12 as A12, KXTEMPT1.A13 as A13, KXTEMPT1.A14 as A14, KXTEMPT1.A15 as A15, KXTEMPT1.A16 as A16, KXTEMPT1.DISP_ID as DISP_ID, KXTEMPT1.ACCOUNT_ID as ACCOUNT_ID, KXTEMPT1.TYPE as TYPE, KXTEMPT1.DISTRICT_ID_2 as DISTRICT_ID_2, KXTEMPT1.FREQUENCY as FREQUENCY, KXTEMPT1.”DATE” as “DATE”, (days_between(KXTEMPT1.DATE_BIRTH , to_timestamp(‘2014-08-05 16:57:18′)) / 3.6525e2) as AGE, CORRT4.Expr_18 as QUARTERLYTRANSACTION_4Q4B_OPERATION_COLLECTIONFROMANOTHERBANK_SUM_0_AMOUNT, CORRT4.Expr_19 as QUARTERLYTRANSACTION_4Q4B_OPERATION_CREDITCARDWITHDRAWAL_SUM_0_AMOUNT, CORRT4.Expr_20 as QUARTERLYTRANSACTION_4Q4B_OPERATION_CREDITINCASH_SUM_0_AMOUNT, CORRT4.Expr_21 as QUARTERLYTRANSACTION_4Q4B_OPERATION_REMITTANCETOANOTHERBANK_SUM_0_AMOUNT, CORRT4.Expr_22 as QUARTERLYTRANSACTION_4Q4B_OPERATION_WITHDRAWALINCASH_SUM_0_AMOUNT, CORRT4.Expr_23 as QUARTERLYTRANSACTION_4Q4B_SUM_0_AMOUNT, CORRT3.Expr_26 as QUARTERLYTRANSACTION_4Q4B_OPERATION_COLLECTIONFROMANOTHERBANK_SUM_1_AMOUNT, CORRT3.Expr_27 as QUARTERLYTRANSACTION_4Q4B_OPERATION_CREDITCARDWITHDRAWAL_SUM_1_AMOUNT, CORRT3.Expr_28 as QUARTERLYTRANSACTION_4Q4B_OPERATION_CREDITINCASH_SUM_1_AMOUNT, CORRT3.Expr_29 as QUARTERLYTRANSACTION_4Q4B_OPERATION_REMITTANCETOANOTHERBANK_SUM_1_AMOUNT, CORRT3.Expr_30 as QUARTERLYTRANSACTION_4Q4B_OPERATION_WITHDRAWALINCASH_SUM_1_AMOUNT, CORRT3.Expr_31 as QUARTERLYTRANSACTION_4Q4B_SUM_1_AMOUNT, CORRT2.Expr_34 as QUARTERLYTRANSACTION_4Q4B_OPERATION_COLLECTIONFROMANOTHERBANK_SUM_2_AMOUNT, CORRT2.Expr_35 as QUARTERLYTRANSACTION_4Q4B_OPERATION_CREDITCARDWITHDRAWAL_SUM_2_AMOUNT, CORRT2.Expr_36 as QUARTERLYTRANSACTION_4Q4B_OPERATION_CREDITINCASH_SUM_2_AMOUNT, CORRT2.Expr_37 as QUARTERLYTRANSACTION_4Q4B_OPERATION_REMITTANCETOANOTHERBANK_SUM_2_AMOUNT, CORRT2.Expr_38 as QUARTERLYTRANSACTION_4Q4B_OPERATION_WITHDRAWALINCASH_SUM_2_AMOUNT, CORRT2.Expr_39 as QUARTERLYTRANSACTION_4Q4B_SUM_2_AMOUNT, CORRT1.Expr_40 as QUARTERLYTRANSACTION_4Q4B_OPERATION_COLLECTIONFROMANOTHERBANK_SUM_3_AMOUNT, CORRT1.Expr_41 as QUARTERLYTRANSACTION_4Q4B_OPERATION_CREDITCARDWITHDRAWAL_SUM_3_AMOUNT, CORRT1.Expr_42 as QUARTERLYTRANSACTION_4Q4B_OPERATION_CREDITINCASH_SUM_3_AMOUNT, CORRT1.Expr_43 as QUARTERLYTRANSACTION_4Q4B_OPERATION_REMITTANCETOANOTHERBANK_SUM_3_AMOUNT, CORRT1.Expr_44 as QUARTERLYTRANSACTION_4Q4B_OPERATION_WITHDRAWALINCASH_SUM_3_AMOUNT, CORRT1.Expr_45 as QUARTERLYTRANSACTION_4Q4B_SUM_3_AMOUNT, KXTEMPT1.BUYSCREDITCARDINQUARTERAFTERTIMESTAMP_1Q100A_EXST_0_CARD_ID as BUYSCREDITCARDINQUARTERAFTERTIMESTAMP_1Q100A_EXST_0_CARD_ID from KX_144817069694536ANA_CUSTOMER_TSP_BUYSCREDITCARDNEXTQUARTER_1448170617 KXTEMPT1 left outer join (select row_number() over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0 order by I056450TRANSACTION.”DATE” asc) as rn,  KXTEMPT1.REFT3_KXRN0 as REFT3_KXRN0, I056450TRANSACTION.ACCOUNT_ID as JKey, sum( case when (I056450TRANSACTION.OPERATION in (N’WITHDRAWAL IN CASH’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_44, sum( case when (I056450TRANSACTION.OPERATION in (N’REMITTANCE TO ANOTHER BANK’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_43, sum( case when (I056450TRANSACTION.OPERATION in (N’CREDIT IN CASH’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_42, sum( case when (I056450TRANSACTION.OPERATION in (N’CREDIT CARD WITHDRAWAL’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_41, sum( case when (I056450TRANSACTION.OPERATION in (N’COLLECTION FROM ANOTHER BANK’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_40, sum(I056450TRANSACTION.AMOUNT) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_45 from KX_144817069694536ANA_CUSTOMER_TSP_BUYSCREDITCARDNEXTQUARTER_1448170617 KXTEMPT1,  I056450.”TRANSACTION”  I056450TRANSACTION  where ((I056450TRANSACTION.ACCOUNT_ID = KXTEMPT1.ACCOUNT_ID) and ((I056450TRANSACTION.”DATE” >= add_months(to_timestamp(‘2014-08-05 16:57:18’) , -3)) and (I056450TRANSACTION.”DATE” < to_timestamp(‘2014-08-05 16:57:18′)))) ) CORRT1 on ((CORRT1.JKey = KXTEMPT1.ACCOUNT_ID) and ( KXTEMPT1.REFT3_KXRN0 = CORRT1.REFT3_KXRN0) and (CORRT1.rn = 1)) left outer join (select row_number() over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0 order by I056450TRANSACTION.”DATE” asc) as rn,  KXTEMPT1.REFT3_KXRN0 as REFT3_KXRN0, I056450TRANSACTION.ACCOUNT_ID as JKey, sum( case when (I056450TRANSACTION.OPERATION in (N’WITHDRAWAL IN CASH’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_38, sum( case when (I056450TRANSACTION.OPERATION in (N’REMITTANCE TO ANOTHER BANK’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_37, sum( case when (I056450TRANSACTION.OPERATION in (N’CREDIT IN CASH’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_36, sum( case when (I056450TRANSACTION.OPERATION in (N’CREDIT CARD WITHDRAWAL’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_35, sum( case when (I056450TRANSACTION.OPERATION in (N’COLLECTION FROM ANOTHER BANK’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_34, sum(I056450TRANSACTION.AMOUNT) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_39 from KX_144817069694536ANA_CUSTOMER_TSP_BUYSCREDITCARDNEXTQUARTER_1448170617 KXTEMPT1,  I056450.”TRANSACTION”  I056450TRANSACTION  where ((I056450TRANSACTION.ACCOUNT_ID = KXTEMPT1.ACCOUNT_ID) and ((I056450TRANSACTION.”DATE” >= add_months(to_timestamp(‘2014-08-05 16:57:18’) , -6)) and (I056450TRANSACTION.”DATE” < add_months(to_timestamp(‘2014-08-05 16:57:18′) , -3)))) ) CORRT2 on ((CORRT2.JKey = KXTEMPT1.ACCOUNT_ID) and ( KXTEMPT1.REFT3_KXRN0 = CORRT2.REFT3_KXRN0) and (CORRT2.rn = 1)) left outer join (select row_number() over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0 order by I056450TRANSACTION.”DATE” asc) as rn,  KXTEMPT1.REFT3_KXRN0 as REFT3_KXRN0, I056450TRANSACTION.ACCOUNT_ID as JKey, sum( case when (I056450TRANSACTION.OPERATION in (N’WITHDRAWAL IN CASH’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_30, sum( case when (I056450TRANSACTION.OPERATION in (N’REMITTANCE TO ANOTHER BANK’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_29, sum( case when (I056450TRANSACTION.OPERATION in (N’CREDIT IN CASH’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_28, sum( case when (I056450TRANSACTION.OPERATION in (N’CREDIT CARD WITHDRAWAL’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_27, sum( case when (I056450TRANSACTION.OPERATION in (N’COLLECTION FROM ANOTHER BANK’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_26, sum(I056450TRANSACTION.AMOUNT) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_31 from KX_144817069694536ANA_CUSTOMER_TSP_BUYSCREDITCARDNEXTQUARTER_1448170617 KXTEMPT1,  I056450.”TRANSACTION”  I056450TRANSACTION  where ((I056450TRANSACTION.ACCOUNT_ID = KXTEMPT1.ACCOUNT_ID) and ((I056450TRANSACTION.”DATE” >= add_months(to_timestamp(‘2014-08-05 16:57:18’) , -9)) and (I056450TRANSACTION.”DATE” < add_months(to_timestamp(‘2014-08-05 16:57:18′) , -6)))) ) CORRT3 on ((CORRT3.JKey = KXTEMPT1.ACCOUNT_ID) and ( KXTEMPT1.REFT3_KXRN0 = CORRT3.REFT3_KXRN0) and (CORRT3.rn = 1)) left outer join (select row_number() over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0 order by I056450TRANSACTION.”DATE” asc) as rn,  KXTEMPT1.REFT3_KXRN0 as REFT3_KXRN0, I056450TRANSACTION.ACCOUNT_ID as JKey, sum( case when (I056450TRANSACTION.OPERATION in (N’WITHDRAWAL IN CASH’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_22, sum( case when (I056450TRANSACTION.OPERATION in (N’REMITTANCE TO ANOTHER BANK’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_21, sum( case when (I056450TRANSACTION.OPERATION in (N’CREDIT IN CASH’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_20, sum( case when (I056450TRANSACTION.OPERATION in (N’CREDIT CARD WITHDRAWAL’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_19, sum( case when (I056450TRANSACTION.OPERATION in (N’COLLECTION FROM ANOTHER BANK’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_18, sum(I056450TRANSACTION.AMOUNT) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_23 from KX_144817069694536ANA_CUSTOMER_TSP_BUYSCREDITCARDNEXTQUARTER_1448170617 KXTEMPT1,  I056450.”TRANSACTION”  I056450TRANSACTION  where ((I056450TRANSACTION.ACCOUNT_ID = KXTEMPT1.ACCOUNT_ID) and ((I056450TRANSACTION.”DATE” >= add_months(to_timestamp(‘2014-08-05 16:57:18’) , -12)) and (I056450TRANSACTION.”DATE” < add_months(to_timestamp(‘2014-08-05 16:57:18’) , -9)))) ) CORRT4 on ((CORRT4.JKey = KXTEMPT1.ACCOUNT_ID) and ( KXTEMPT1.REFT3_KXRN0 = CORRT4.REFT3_KXRN0) and (CORRT4.rn = 1)) order by CLIENT_ID</SQL></SelectSQL></SQLScript>.

      KXAF needs the number of cases of <SQLScript><IntermediateEntities><Entity><Type>TABLE</Type><Name>KX_144817069696536TSP_BUYSCREDITCARDNEXTQUARTER</Name></Entity><Entity><Type>TABLE</Type><Name>KX_144817069798536TSP_BUYSCREDITCARDNEXTQUARTER</Name></Entity><Entity><Type>TABLE</Type><Name>KX_144817069694536ANA_CUSTOMER_TSP_BUYSCREDITCARDNEXTQUARTER_1448170617</Name></Entity></IntermediateEntities><CleanSQL><SQL>DROP TABLE KX_144817069696536TSP_BUYSCREDITCARDNEXTQUARTER</SQL><SQL>DROP TABLE KX_144817069798536TSP_BUYSCREDITCARDNEXTQUARTER</SQL><SQL>DROP TABLE KX_144817069694536ANA_CUSTOMER_TSP_BUYSCREDITCARDNEXTQUARTER_1448170617</SQL></CleanSQL><CreateSQL><SQL>CREATE COLUMN TABLE KX_144817069696536TSP_BUYSCREDITCARDNEXTQUARTER (KXID BIGINT,DISP_ID BIGINT,  PRIMARY KEY (KXID))</SQL><SQL>INSERT INTO KX_144817069696536TSP_BUYSCREDITCARDNEXTQUARTER select ENT_CUSTOMER.KXID as KXID, I056450DISPOSITION.DISP_ID as DISP_ID from (select I056450CLIENT.CLIENT_ID as KXID from I056450.CLIENT I056450CLIENT)  ENT_CUSTOMER left outer join I056450.DISPOSITION I056450DISPOSITION on (ENT_CUSTOMER.KXID = I056450DISPOSITION.CLIENT_ID)</SQL><SQL>CREATE COLUMN TABLE KX_144817069798536TSP_BUYSCREDITCARDNEXTQUARTER (KXID BIGINT,KXTIMESTAMP TIMESTAMP,BUYSCREDITCARDINQUARTERAFTERTIMESTAMP_1Q100A_EXST_0_CARD_ID BIGINT,  PRIMARY KEY (KXID,KXTIMESTAMP))</SQL><SQL>INSERT INTO KX_144817069798536TSP_BUYSCREDITCARDNEXTQUARTER select KXTEMPT1.KXID as KXID, to_timestamp(‘2014-08-05 16:57:18’) as KXTIMESTAMP, case when ((exists (select ‘1’ from I056450.CARD  I056450CARD where ((I056450CARD.DISP_ID = KXTEMPT1.DISP_ID) and ((I056450CARD.ISSUED >= to_timestamp(‘2014-08-05 16:57:18’)) and (I056450CARD.ISSUED < add_months(to_timestamp(‘2014-08-05 16:57:18’) , 3))))))) then 1 else 0 end  as BUYSCREDITCARDINQUARTERAFTERTIMESTAMP_1Q100A_EXST_0_CARD_ID from KX_144817069696536TSP_BUYSCREDITCARDNEXTQUARTER KXTEMPT1 where (case when ((not exists (select ‘1’ from I056450.CARD  I056450CARD where ((I056450CARD.DISP_ID = KXTEMPT1.DISP_ID) and ((I056450CARD.ISSUED >= add_years(to_timestamp(‘2014-08-05 16:57:18’) , -100)) and (I056450CARD.ISSUED < to_timestamp(‘2014-08-05 16:57:18’))))))) then 1 else 0 end  = 1)</SQL><SQL>CREATE COLUMN TABLE KX_144817069694536ANA_CUSTOMER_TSP_BUYSCREDITCARDNEXTQUARTER_1448170617 (KXID BIGINT,KXTIMESTAMP TIMESTAMP,CLIENT_ID BIGINT,DISTRICT_ID BIGINT,GENDER NVARCHAR(255),DATE_BIRTH DATE,A2 NVARCHAR(255),A3 NVARCHAR(255),A4 BIGINT,A5 BIGINT,A6 BIGINT,A7 BIGINT,A8 BIGINT,A9 BIGINT,A10 DOUBLE,A11 BIGINT,A12 NVARCHAR(255),A13 DOUBLE,A14 BIGINT,A15 NVARCHAR(255),A16 BIGINT,DISP_ID BIGINT,ACCOUNT_ID BIGINT,TYPE NVARCHAR(255),DISTRICT_ID_2 BIGINT,FREQUENCY NVARCHAR(255),”DATE” DATE,BUYSCREDITCARDINQUARTERAFTERTIMESTAMP_1Q100A_EXST_0_CARD_ID BIGINT,REFT3_KXRN0 BIGINT,  PRIMARY KEY (KXID,KXTIMESTAMP,CLIENT_ID))</SQL><SQL>INSERT INTO KX_144817069694536ANA_CUSTOMER_TSP_BUYSCREDITCARDNEXTQUARTER_1448170617 select ENT_CUSTOMERPOPULATION.KXID as KXID, to_timestamp(‘2014-08-05 16:57:18’) as KXTIMESTAMP, I056450CLIENT.CLIENT_ID as CLIENT_ID, I056450CLIENT.DISTRICT_ID as DISTRICT_ID, I056450CLIENT.GENDER as GENDER, I056450CLIENT.DATE_BIRTH as DATE_BIRTH, I056450DISTRICT.A2 as A2, I056450DISTRICT.A3 as A3, I056450DISTRICT.A4 as A4, I056450DISTRICT.A5 as A5, I056450DISTRICT.A6 as A6, I056450DISTRICT.A7 as A7, I056450DISTRICT.A8 as A8, I056450DISTRICT.A9 as A9, I056450DISTRICT.A10 as A10, I056450DISTRICT.A11 as A11, I056450DISTRICT.A12 as A12, I056450DISTRICT.A13 as A13, I056450DISTRICT.A14 as A14, I056450DISTRICT.A15 as A15, I056450DISTRICT.A16 as A16, I056450DISPOSITION.DISP_ID as DISP_ID, I056450DISPOSITION.ACCOUNT_ID as ACCOUNT_ID, I056450DISPOSITION.TYPE as TYPE, I056450ACCOUNT.DISTRICT_ID as DISTRICT_ID_2, I056450ACCOUNT.FREQUENCY as FREQUENCY, I056450ACCOUNT.”DATE” as “DATE”, ENT_CUSTOMERPOPULATION.BUYSCREDITCARDINQUARTERAFTERTIMESTAMP_1Q100A_EXST_0_CARD_ID as BUYSCREDITCARDINQUARTERAFTERTIMESTAMP_1Q100A_EXST_0_CARD_ID, I056450DISPOSITION.REFT3_KXRN0 from KX_144817069798536TSP_BUYSCREDITCARDNEXTQUARTER  ENT_CUSTOMERPOPULATION left outer join I056450.CLIENT I056450CLIENT on (ENT_CUSTOMERPOPULATION.KXID = I056450CLIENT.CLIENT_ID) left outer join I056450.DISTRICT I056450DISTRICT on (I056450CLIENT.DISTRICT_ID = I056450DISTRICT.DISTRICT_ID) left outer join (select REFT.*, row_number() over (partition by REFT.ACCOUNT_ID order by REFT.ACCOUNT_ID asc) as REFT3_KXRN0 from I056450.DISPOSITION REFT) I056450DISPOSITION on (ENT_CUSTOMERPOPULATION.KXID = I056450DISPOSITION.CLIENT_ID) left outer join I056450.ACCOUNT I056450ACCOUNT on (ENT_CUSTOMERPOPULATION.KXID = I056450ACCOUNT.ACCOUNT_ID)</SQL></CreateSQL><SelectSQL><SQL>select KXTEMPT1.KXID as KXID, to_timestamp(‘2014-08-05 16:57:18’) as KXTIMESTAMP, KXTEMPT1.CLIENT_ID as CLIENT_ID, KXTEMPT1.DISTRICT_ID as DISTRICT_ID, KXTEMPT1.GENDER as GENDER, KXTEMPT1.A2 as A2, KXTEMPT1.A3 as A3, KXTEMPT1.A4 as A4, KXTEMPT1.A5 as A5, KXTEMPT1.A6 as A6, KXTEMPT1.A7 as A7, KXTEMPT1.A8 as A8, KXTEMPT1.A9 as A9, KXTEMPT1.A10 as A10, KXTEMPT1.A11 as A11, KXTEMPT1.A12 as A12, KXTEMPT1.A13 as A13, KXTEMPT1.A14 as A14, KXTEMPT1.A15 as A15, KXTEMPT1.A16 as A16, KXTEMPT1.DISP_ID as DISP_ID, KXTEMPT1.ACCOUNT_ID as ACCOUNT_ID, KXTEMPT1.TYPE as TYPE, KXTEMPT1.DISTRICT_ID_2 as DISTRICT_ID_2, KXTEMPT1.FREQUENCY as FREQUENCY, KXTEMPT1.”DATE” as “DATE”, (days_between(KXTEMPT1.DATE_BIRTH , to_timestamp(‘2014-08-05 16:57:18′)) / 3.6525e2) as AGE, CORRT4.Expr_18 as QUARTERLYTRANSACTION_4Q4B_OPERATION_COLLECTIONFROMANOTHERBANK_SUM_0_AMOUNT, CORRT4.Expr_19 as QUARTERLYTRANSACTION_4Q4B_OPERATION_CREDITCARDWITHDRAWAL_SUM_0_AMOUNT, CORRT4.Expr_20 as QUARTERLYTRANSACTION_4Q4B_OPERATION_CREDITINCASH_SUM_0_AMOUNT, CORRT4.Expr_21 as QUARTERLYTRANSACTION_4Q4B_OPERATION_REMITTANCETOANOTHERBANK_SUM_0_AMOUNT, CORRT4.Expr_22 as QUARTERLYTRANSACTION_4Q4B_OPERATION_WITHDRAWALINCASH_SUM_0_AMOUNT, CORRT4.Expr_23 as QUARTERLYTRANSACTION_4Q4B_SUM_0_AMOUNT, CORRT3.Expr_26 as QUARTERLYTRANSACTION_4Q4B_OPERATION_COLLECTIONFROMANOTHERBANK_SUM_1_AMOUNT, CORRT3.Expr_27 as QUARTERLYTRANSACTION_4Q4B_OPERATION_CREDITCARDWITHDRAWAL_SUM_1_AMOUNT, CORRT3.Expr_28 as QUARTERLYTRANSACTION_4Q4B_OPERATION_CREDITINCASH_SUM_1_AMOUNT, CORRT3.Expr_29 as QUARTERLYTRANSACTION_4Q4B_OPERATION_REMITTANCETOANOTHERBANK_SUM_1_AMOUNT, CORRT3.Expr_30 as QUARTERLYTRANSACTION_4Q4B_OPERATION_WITHDRAWALINCASH_SUM_1_AMOUNT, CORRT3.Expr_31 as QUARTERLYTRANSACTION_4Q4B_SUM_1_AMOUNT, CORRT2.Expr_34 as QUARTERLYTRANSACTION_4Q4B_OPERATION_COLLECTIONFROMANOTHERBANK_SUM_2_AMOUNT, CORRT2.Expr_35 as QUARTERLYTRANSACTION_4Q4B_OPERATION_CREDITCARDWITHDRAWAL_SUM_2_AMOUNT, CORRT2.Expr_36 as QUARTERLYTRANSACTION_4Q4B_OPERATION_CREDITINCASH_SUM_2_AMOUNT, CORRT2.Expr_37 as QUARTERLYTRANSACTION_4Q4B_OPERATION_REMITTANCETOANOTHERBANK_SUM_2_AMOUNT, CORRT2.Expr_38 as QUARTERLYTRANSACTION_4Q4B_OPERATION_WITHDRAWALINCASH_SUM_2_AMOUNT, CORRT2.Expr_39 as QUARTERLYTRANSACTION_4Q4B_SUM_2_AMOUNT, CORRT1.Expr_40 as QUARTERLYTRANSACTION_4Q4B_OPERATION_COLLECTIONFROMANOTHERBANK_SUM_3_AMOUNT, CORRT1.Expr_41 as QUARTERLYTRANSACTION_4Q4B_OPERATION_CREDITCARDWITHDRAWAL_SUM_3_AMOUNT, CORRT1.Expr_42 as QUARTERLYTRANSACTION_4Q4B_OPERATION_CREDITINCASH_SUM_3_AMOUNT, CORRT1.Expr_43 as QUARTERLYTRANSACTION_4Q4B_OPERATION_REMITTANCETOANOTHERBANK_SUM_3_AMOUNT, CORRT1.Expr_44 as QUARTERLYTRANSACTION_4Q4B_OPERATION_WITHDRAWALINCASH_SUM_3_AMOUNT, CORRT1.Expr_45 as QUARTERLYTRANSACTION_4Q4B_SUM_3_AMOUNT, KXTEMPT1.BUYSCREDITCARDINQUARTERAFTERTIMESTAMP_1Q100A_EXST_0_CARD_ID as BUYSCREDITCARDINQUARTERAFTERTIMESTAMP_1Q100A_EXST_0_CARD_ID from KX_144817069694536ANA_CUSTOMER_TSP_BUYSCREDITCARDNEXTQUARTER_1448170617 KXTEMPT1 left outer join (select row_number() over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0 order by I056450TRANSACTION.”DATE” asc) as rn,  KXTEMPT1.REFT3_KXRN0 as REFT3_KXRN0, I056450TRANSACTION.ACCOUNT_ID as JKey, sum( case when (I056450TRANSACTION.OPERATION in (N’WITHDRAWAL IN CASH’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_44, sum( case when (I056450TRANSACTION.OPERATION in (N’REMITTANCE TO ANOTHER BANK’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_43, sum( case when (I056450TRANSACTION.OPERATION in (N’CREDIT IN CASH’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_42, sum( case when (I056450TRANSACTION.OPERATION in (N’CREDIT CARD WITHDRAWAL’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_41, sum( case when (I056450TRANSACTION.OPERATION in (N’COLLECTION FROM ANOTHER BANK’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_40, sum(I056450TRANSACTION.AMOUNT) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_45 from KX_144817069694536ANA_CUSTOMER_TSP_BUYSCREDITCARDNEXTQUARTER_1448170617 KXTEMPT1,  I056450.”TRANSACTION”  I056450TRANSACTION  where ((I056450TRANSACTION.ACCOUNT_ID = KXTEMPT1.ACCOUNT_ID) and ((I056450TRANSACTION.”DATE” >= add_months(to_timestamp(‘2014-08-05 16:57:18’) , -3)) and (I056450TRANSACTION.”DATE” < to_timestamp(‘2014-08-05 16:57:18′)))) ) CORRT1 on ((CORRT1.JKey = KXTEMPT1.ACCOUNT_ID) and ( KXTEMPT1.REFT3_KXRN0 = CORRT1.REFT3_KXRN0) and (CORRT1.rn = 1)) left outer join (select row_number() over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0 order by I056450TRANSACTION.”DATE” asc) as rn,  KXTEMPT1.REFT3_KXRN0 as REFT3_KXRN0, I056450TRANSACTION.ACCOUNT_ID as JKey, sum( case when (I056450TRANSACTION.OPERATION in (N’WITHDRAWAL IN CASH’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_38, sum( case when (I056450TRANSACTION.OPERATION in (N’REMITTANCE TO ANOTHER BANK’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_37, sum( case when (I056450TRANSACTION.OPERATION in (N’CREDIT IN CASH’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_36, sum( case when (I056450TRANSACTION.OPERATION in (N’CREDIT CARD WITHDRAWAL’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_35, sum( case when (I056450TRANSACTION.OPERATION in (N’COLLECTION FROM ANOTHER BANK’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_34, sum(I056450TRANSACTION.AMOUNT) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_39 from KX_144817069694536ANA_CUSTOMER_TSP_BUYSCREDITCARDNEXTQUARTER_1448170617 KXTEMPT1,  I056450.”TRANSACTION”  I056450TRANSACTION  where ((I056450TRANSACTION.ACCOUNT_ID = KXTEMPT1.ACCOUNT_ID) and ((I056450TRANSACTION.”DATE” >= add_months(to_timestamp(‘2014-08-05 16:57:18’) , -6)) and (I056450TRANSACTION.”DATE” < add_months(to_timestamp(‘2014-08-05 16:57:18′) , -3)))) ) CORRT2 on ((CORRT2.JKey = KXTEMPT1.ACCOUNT_ID) and ( KXTEMPT1.REFT3_KXRN0 = CORRT2.REFT3_KXRN0) and (CORRT2.rn = 1)) left outer join (select row_number() over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0 order by I056450TRANSACTION.”DATE” asc) as rn,  KXTEMPT1.REFT3_KXRN0 as REFT3_KXRN0, I056450TRANSACTION.ACCOUNT_ID as JKey, sum( case when (I056450TRANSACTION.OPERATION in (N’WITHDRAWAL IN CASH’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_30, sum( case when (I056450TRANSACTION.OPERATION in (N’REMITTANCE TO ANOTHER BANK’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_29, sum( case when (I056450TRANSACTION.OPERATION in (N’CREDIT IN CASH’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_28, sum( case when (I056450TRANSACTION.OPERATION in (N’CREDIT CARD WITHDRAWAL’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_27, sum( case when (I056450TRANSACTION.OPERATION in (N’COLLECTION FROM ANOTHER BANK’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_26, sum(I056450TRANSACTION.AMOUNT) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_31 from KX_144817069694536ANA_CUSTOMER_TSP_BUYSCREDITCARDNEXTQUARTER_1448170617 KXTEMPT1,  I056450.”TRANSACTION”  I056450TRANSACTION  where ((I056450TRANSACTION.ACCOUNT_ID = KXTEMPT1.ACCOUNT_ID) and ((I056450TRANSACTION.”DATE” >= add_months(to_timestamp(‘2014-08-05 16:57:18’) , -9)) and (I056450TRANSACTION.”DATE” < add_months(to_timestamp(‘2014-08-05 16:57:18′) , -6)))) ) CORRT3 on ((CORRT3.JKey = KXTEMPT1.ACCOUNT_ID) and ( KXTEMPT1.REFT3_KXRN0 = CORRT3.REFT3_KXRN0) and (CORRT3.rn = 1)) left outer join (select row_number() over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0 order by I056450TRANSACTION.”DATE” asc) as rn,  KXTEMPT1.REFT3_KXRN0 as REFT3_KXRN0, I056450TRANSACTION.ACCOUNT_ID as JKey, sum( case when (I056450TRANSACTION.OPERATION in (N’WITHDRAWAL IN CASH’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_22, sum( case when (I056450TRANSACTION.OPERATION in (N’REMITTANCE TO ANOTHER BANK’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_21, sum( case when (I056450TRANSACTION.OPERATION in (N’CREDIT IN CASH’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_20, sum( case when (I056450TRANSACTION.OPERATION in (N’CREDIT CARD WITHDRAWAL’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_19, sum( case when (I056450TRANSACTION.OPERATION in (N’COLLECTION FROM ANOTHER BANK’)) then I056450TRANSACTION.AMOUNT else NULL end ) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_18, sum(I056450TRANSACTION.AMOUNT) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_23 from KX_144817069694536ANA_CUSTOMER_TSP_BUYSCREDITCARDNEXTQUARTER_1448170617 KXTEMPT1,  I056450.”TRANSACTION”  I056450TRANSACTION  where ((I056450TRANSACTION.ACCOUNT_ID = KXTEMPT1.ACCOUNT_ID) and ((I056450TRANSACTION.”DATE” >= add_months(to_timestamp(‘2014-08-05 16:57:18’) , -12)) and (I056450TRANSACTION.”DATE” < add_months(to_timestamp(‘2014-08-05 16:57:18’) , -9)))) ) CORRT4 on ((CORRT4.JKey = KXTEMPT1.ACCOUNT_ID) and ( KXTEMPT1.REFT3_KXRN0 = CORRT4.REFT3_KXRN0) and (CORRT4.rn = 1)) order by CLIENT_ID</SQL></SelectSQL></SQLScript> but cannot get it in an efficient way.

      This operation may take some time.

      Some intermediate entities missing in script KX_144817069696536TSP_BUYSCREDITCARDNEXTQUARTER.

      Some intermediate entities missing in script KX_144817069696536TSP_BUYSCREDITCARDNEXTQUARTER.

      Some intermediate entities missing in script KX_144817069696536TSP_BUYSCREDITCARDNEXTQUARTER.

      Cleaning intermediate entities of script KX_144817069696536TSP_BUYSCREDITCARDNEXTQUARTER.

      Creating intermediate entities of script KX_144817069696536TSP_BUYSCREDITCARDNEXTQUARTER.

      The cache on data set ANA_CUSTOMER_TSP_BuysCreditCardNextQuarter_1448170617 (Training)  is activated.

      ANA_CUSTOMER_TSP_BuysCreditCardNextQuarter_1448170617(Training)  has been loaded in cache: there are 4700 elements in L1 and 0 in L2.

      Size of ANA_CUSTOMER_TSP_BuysCreditCardNextQuarter_1448170617(Training)  in Data Cache L1 is 1.1 MB and L2 is 0.

      Beginning of learning for Default.

      Computing statistics…

      Computing statistics…

      Statistics for discrete target ‘BuysCreditCardInQuarterAfterTimeStamp_1Q100A_EXST_0_CARD_ID’:

      On Estimation, ‘0’ is found 2654 times.

      On Estimation, ‘1’ is found 78 times.

      On Validation, ‘0’ is found 1004 times.

      On Validation, ‘1’ is found 24 times.

      Variable ‘GENDER’ compression on estimation: from 2 to 1 categories

      Variable ‘GENDER’ compression on validation: from 2 to 1 categories

      Variable ‘A2’ compression on estimation: from 77 to 29 categories

      Variable ‘A2’ compression on validation: from 77 to 29 categories

      Variable ‘A3’ compression on estimation: from 8 to 5 categories

      Variable ‘A3’ compression on validation: from 8 to 5 categories

      Variable ‘A12’ compression on estimation: from 71 to 24 categories

      Variable ‘A12’ compression on validation: from 71 to 24 categories

      Variable ‘A15’ compression on estimation: from 76 to 29 categories

      Variable ‘A15’ compression on validation: from 76 to 29 categories

      Variable ‘FREQUENCY’ compression on estimation: from 4 to 1 categories

      Variable ‘FREQUENCY’ compression on validation: from 4 to 1 categories

      Variable ‘QuarterlyTransaction_4Q4B_OPERATION_CREDITCARDWITHDRAWAL_SUM_0_AMOUNT’ compression on estimation: from 2 to 1 categories

      Variable ‘QuarterlyTransaction_4Q4B_OPERATION_CREDITCARDWITHDRAWAL_SUM_0_AMOUNT’ compression on validation: from 2 to 1 categories

      Variable ‘QuarterlyTransaction_4Q4B_OPERATION_CREDITINCASH_SUM_0_AMOUNT’ compression on estimation: from 10 to 5 categories

      Variable ‘QuarterlyTransaction_4Q4B_OPERATION_CREDITINCASH_SUM_0_AMOUNT’ compression on validation: from 10 to 5 categories

      Variable ‘QuarterlyTransaction_4Q4B_OPERATION_WITHDRAWALINCASH_SUM_0_AMOUNT’ compression on estimation: from 6 to 2 categories

      Variable ‘QuarterlyTransaction_4Q4B_OPERATION_WITHDRAWALINCASH_SUM_0_AMOUNT’ compression on validation: from 6 to 2 categories

      Variable ‘QuarterlyTransaction_4Q4B_SUM_0_AMOUNT’ compression on estimation: from 4 to 1 categories

      Variable ‘QuarterlyTransaction_4Q4B_SUM_0_AMOUNT’ compression on validation: from 4 to 1 categories

      Variable ‘QuarterlyTransaction_4Q4B_OPERATION_CREDITCARDWITHDRAWAL_SUM_1_AMOUNT’ compression on estimation: from 2 to 1 categories

      Variable ‘QuarterlyTransaction_4Q4B_OPERATION_CREDITCARDWITHDRAWAL_SUM_1_AMOUNT’ compression on validation: from 2 to 1 categories

      Variable ‘QuarterlyTransaction_4Q4B_OPERATION_CREDITINCASH_SUM_1_AMOUNT’ compression on estimation: from 4 to 2 categories

      Variable ‘QuarterlyTransaction_4Q4B_OPERATION_CREDITINCASH_SUM_1_AMOUNT’ compression on validation: from 4 to 2 categories

      Variable ‘QuarterlyTransaction_4Q4B_OPERATION_WITHDRAWALINCASH_SUM_1_AMOUNT’ compression on estimation: from 2 to 1 categories

      Variable ‘QuarterlyTransaction_4Q4B_OPERATION_WITHDRAWALINCASH_SUM_1_AMOUNT’ compression on validation: from 2 to 1 categories

      Variable ‘QuarterlyTransaction_4Q4B_SUM_1_AMOUNT’ compression on estimation: from 2 to 1 categories

      Variable ‘QuarterlyTransaction_4Q4B_SUM_1_AMOUNT’ compression on validation: from 2 to 1 categories

      Variable ‘QuarterlyTransaction_4Q4B_OPERATION_CREDITCARDWITHDRAWAL_SUM_2_AMOUNT’ compression on estimation: from 2 to 1 categories

      Variable ‘QuarterlyTransaction_4Q4B_OPERATION_CREDITCARDWITHDRAWAL_SUM_2_AMOUNT’ compression on validation: from 2 to 1 categories

      Variable ‘QuarterlyTransaction_4Q4B_OPERATION_WITHDRAWALINCASH_SUM_2_AMOUNT’ compression on estimation: from 6 to 1 categories

      Variable ‘QuarterlyTransaction_4Q4B_OPERATION_WITHDRAWALINCASH_SUM_2_AMOUNT’ compression on validation: from 6 to 1 categories

      Variable ‘QuarterlyTransaction_4Q4B_SUM_2_AMOUNT’ compression on estimation: from 2 to 1 categories

      Variable ‘QuarterlyTransaction_4Q4B_SUM_2_AMOUNT’ compression on validation: from 2 to 1 categories

      Variable ‘QuarterlyTransaction_4Q4B_OPERATION_CREDITCARDWITHDRAWAL_SUM_3_AMOUNT’ compression on estimation: from 2 to 1 categories

      Variable ‘QuarterlyTransaction_4Q4B_OPERATION_CREDITCARDWITHDRAWAL_SUM_3_AMOUNT’ compression on validation: from 2 to 1 categories

      Variable ‘QuarterlyTransaction_4Q4B_OPERATION_CREDITINCASH_SUM_3_AMOUNT’ compression on estimation: from 3 to 2 categories

      Variable ‘QuarterlyTransaction_4Q4B_OPERATION_CREDITINCASH_SUM_3_AMOUNT’ compression on validation: from 3 to 2 categories

      Variable ‘QuarterlyTransaction_4Q4B_OPERATION_WITHDRAWALINCASH_SUM_3_AMOUNT’ compression on estimation: from 5 to 1 categories

      Variable ‘QuarterlyTransaction_4Q4B_OPERATION_WITHDRAWALINCASH_SUM_3_AMOUNT’ compression on validation: from 5 to 1 categories

      Variable ‘QuarterlyTransaction_4Q4B_SUM_3_AMOUNT’ compression on estimation: from 2 to 1 categories

      Variable ‘QuarterlyTransaction_4Q4B_SUM_3_AMOUNT’ compression on validation: from 2 to 1 categories

      Best individual variables KI: A2 = 0.3254 (Validation) 0.3436 (Estimation)

      Best individual variables KI: A15 = 0.3254 (Validation) 0.3436 (Estimation)

      Best individual variables KI: A16 = 0.2908 (Validation) 0.1351 (Estimation)

      Best individual variables KI: A13 = 0.2873 (Validation) 0.0957 (Estimation)

      Best individual variables KI: A4 = 0.2762 (Validation) 0.107 (Estimation)

      Best individual variables KI: QuarterlyTransaction_4Q4B_OPERATION_REMITTANCETOANOTHERBANK_SUM_0_AMOUNT = 0.2711 (Validation) 0.1171 (Estimation)

      Best individual variables KI: A12 = 0.2623 (Validation) 0.2679 (Estimation)

      Best individual variables KI: QuarterlyTransaction_4Q4B_OPERATION_CREDITINCASH_SUM_0_AMOUNT = 0.255 (Validation) 0.1351 (Estimation)

      Best individual variables KI: A14 = 0.2518 (Validation) 0.1654 (Estimation)

      Best individual variables KI: QuarterlyTransaction_4Q4B_OPERATION_COLLECTIONFROMANOTHERBANK_SUM_0_AMOUNT = 0.2479 (Validation) 0.2063 (Estimation)

      Computing statistics…

      Learning time: 1 seconds

      Data encoding learning phase finished

      The variable ‘A10’ has a very low KI on Estimation data set with respect to the target ‘BuysCreditCardInQuarterAfterTimeStamp_1Q100A_EXST_0_CARD_ID’. It will be excluded from the model with respect to this target.

      The variable ‘c_A10’ has a very low KI on Estimation data set with respect to the target ‘BuysCreditCardInQuarterAfterTimeStamp_1Q100A_EXST_0_CARD_ID’. It will be excluded from the model with respect to this target.

      ’13’ Variables  are constant on the Estimation data set. They will be excluded from the model.

      Number of input: 40, order: 1, target key: 1

      Number of extended variables: 41

      Computing statistics…

      Computing statistics…

      Indicator on validation:

      Quality:    KI=0.6177

      Robustness: KR=0.9512

      Standard errors on estimation: L1=0.267197   L2=0.30653   LInf=0.720863

      Standard errors on validation: L1=0.269845   L2=0.307896   LInf=0.728212

      Computing statistics…

      Quality indicator on test: KI=0.0923

      Standard errors on test:       L1=0.302696   L2=0.34275   LInf=1.01868

      Auto-selection…

      Computing statistics…

      Indicator on validation:

      Quality:    KI=0.6297

      Robustness: KR=0.9638

      Standard errors on estimation: L1=0.269258   L2=0.312315   LInf=0.728396

      Standard errors on validation: L1=0.271974   L2=0.314106   LInf=0.727851

      Computing statistics…

      Quality indicator on test: KI=0.1169

      Standard errors on test:       L1=0.306903   L2=0.350918   LInf=1.03997

      Index of current iteration 1

      Reference KI: 0.6177 Reference KR: 0.9512

      Number of kept variables 18

      KI: 0.6297 KR: 0.9638

      For the current iteration: 18 variables kept, KI=0.6297, KR=0.9638

      Computing statistics…

      Indicator on validation:

      Quality:    KI=0.6252

      Robustness: KR=0.966

      Standard errors on estimation: L1=0.266943   L2=0.302462   LInf=0.719726

      Standard errors on validation: L1=0.268893   L2=0.303135   LInf=0.716376

      Computing statistics…

      Quality indicator on test: KI=0.1072

      Standard errors on test:       L1=0.298638   L2=0.334402   LInf=0.953719

      Index of current iteration 2

      Reference KI: 0.6297 Reference KR: 0.9638

      Number of kept variables 14

      KI: 0.6252 KR: 0.966

      For the current iteration: 14 variables kept, KI=0.6252, KR=0.966

      Computing statistics…

      Indicator on validation:

      Quality:    KI=0.6459

      Robustness: KR=0.9733

      Standard errors on estimation: L1=0.277664   L2=0.32549   LInf=0.724148

      Standard errors on validation: L1=0.281918   L2=0.327661   LInf=0.723813

      Computing statistics…

      Quality indicator on test: KI=0.1043

      Standard errors on test:       L1=0.311133   L2=0.361447   LInf=1.05466

      Index of current iteration 3

      Reference KI: 0.6297 Reference KR: 0.9638

      Number of kept variables 11

      KI: 0.6459 KR: 0.9733

      For the current iteration: 11 variables kept, KI=0.6459, KR=0.9733

      Computing statistics…

      Indicator on validation:

      Quality:    KI=0.6399

      Robustness: KR=0.938

      Standard errors on estimation: L1=0.266984   L2=0.308113   LInf=0.733934

      Standard errors on validation: L1=0.271126   L2=0.310617   LInf=0.740932

      Computing statistics…

      Quality indicator on test: KI=0.1066

      Standard errors on test:       L1=0.296918   L2=0.340224   LInf=0.996161

      Index of current iteration 4

      Reference KI: 0.6459 Reference KR: 0.9733

      Number of kept variables 9

      KI: 0.6399 KR: 0.938

      For the current iteration: 9 variables kept, KI=0.6399, KR=0.938

      Computing statistics…

      Indicator on validation:

      Quality:    KI=0.6054

      Robustness: KR=0.9299

      Standard errors on estimation: L1=0.267298   L2=0.296792   LInf=0.747394

      Standard errors on validation: L1=0.271389   L2=0.29844   LInf=0.735462

      Computing statistics…

      Quality indicator on test: KI=0.0814

      Standard errors on test:       L1=0.2923   L2=0.324023   LInf=0.928371

      Index of current iteration 5

      Reference KI: 0.6459 Reference KR: 0.9733

      Number of kept variables 7

      KI: 0.6054 KR: 0.9299

      For the current iteration: 7 variables kept, KI=0.6054, KR=0.9299

      Computing statistics…

      Computing statistics…

      Indicator on validation:

      Quality:    KI=0.6459

      Robustness: KR=0.9733

      Standard errors on estimation: L1=0.277664   L2=0.32549   LInf=0.724148

      Standard errors on validation: L1=0.281918   L2=0.327661   LInf=0.723813

      Computing statistics…

      Quality indicator on test: KI=0.1043

      Standard errors on test:       L1=0.311133   L2=0.361447   LInf=1.05466

      Learning time: 3 seconds

      Classification/regression learning phase finished

      Total elapsed time: 5 seconds.

      Delete objects created after successful execution of script KX_144817069696536TSP_BUYSCREDITCARDNEXTQUARTER.

      Cleaning intermediate entities of script KX_144817069696536TSP_BUYSCREDITCARDNEXTQUARTER.

      End of the training process.

      The apply engine has detected ’33’ discarded variables: [‘A10′;’A13′;’A2′;’A3′;’A4′;’A6′;’A8′;’A9′;’DATE’;’FREQUENCY’;’GENDER’;’QuarterlyTransaction_4Q4B_OPERATION_COLLECTIONFROMANOTHERBANK_SUM_0_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_COLLECTIONFROMANOTHERBANK_SUM_1_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_COLLECTIONFROMANOTHERBANK_SUM_2_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_COLLECTIONFROMANOTHERBANK_SUM_3_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_CREDITCARDWITHDRAWAL_SUM_0_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_CREDITCARDWITHDRAWAL_SUM_1_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_CREDITCARDWITHDRAWAL_SUM_2_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_CREDITCARDWITHDRAWAL_SUM_3_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_CREDITINCASH_SUM_0_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_CREDITINCASH_SUM_2_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_CREDITINCASH_SUM_3_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_REMITTANCETOANOTHERBANK_SUM_0_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_REMITTANCETOANOTHERBANK_SUM_1_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_REMITTANCETOANOTHERBANK_SUM_2_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_REMITTANCETOANOTHERBANK_SUM_3_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_WITHDRAWALINCASH_SUM_1_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_WITHDRAWALINCASH_SUM_2_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_WITHDRAWALINCASH_SUM_3_AMOUNT’;’QuarterlyTransaction_4Q4B_SUM_0_AMOUNT’;’QuarterlyTransaction_4Q4B_SUM_1_AMOUNT’;’QuarterlyTransaction_4Q4B_SUM_2_AMOUNT’;’QuarterlyTransaction_4Q4B_SUM_3_AMOUNT’]

      The apply engine has detected ’33’ discarded variables: [‘A10′;’A13′;’A2′;’A3′;’A4′;’A6′;’A8′;’A9′;’DATE’;’FREQUENCY’;’GENDER’;’QuarterlyTransaction_4Q4B_OPERATION_COLLECTIONFROMANOTHERBANK_SUM_0_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_COLLECTIONFROMANOTHERBANK_SUM_1_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_COLLECTIONFROMANOTHERBANK_SUM_2_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_COLLECTIONFROMANOTHERBANK_SUM_3_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_CREDITCARDWITHDRAWAL_SUM_0_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_CREDITCARDWITHDRAWAL_SUM_1_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_CREDITCARDWITHDRAWAL_SUM_2_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_CREDITCARDWITHDRAWAL_SUM_3_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_CREDITINCASH_SUM_0_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_CREDITINCASH_SUM_2_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_CREDITINCASH_SUM_3_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_REMITTANCETOANOTHERBANK_SUM_0_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_REMITTANCETOANOTHERBANK_SUM_1_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_REMITTANCETOANOTHERBANK_SUM_2_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_REMITTANCETOANOTHERBANK_SUM_3_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_WITHDRAWALINCASH_SUM_1_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_WITHDRAWALINCASH_SUM_2_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_WITHDRAWALINCASH_SUM_3_AMOUNT’;’QuarterlyTransaction_4Q4B_SUM_0_AMOUNT’;’QuarterlyTransaction_4Q4B_SUM_1_AMOUNT’;’QuarterlyTransaction_4Q4B_SUM_2_AMOUNT’;’QuarterlyTransaction_4Q4B_SUM_3_AMOUNT’]

      The apply engine has detected ’33’ discarded variables: [‘A10′;’A13′;’A2′;’A3′;’A4′;’A6′;’A8′;’A9′;’DATE’;’FREQUENCY’;’GENDER’;’QuarterlyTransaction_4Q4B_OPERATION_COLLECTIONFROMANOTHERBANK_SUM_0_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_COLLECTIONFROMANOTHERBANK_SUM_1_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_COLLECTIONFROMANOTHERBANK_SUM_2_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_COLLECTIONFROMANOTHERBANK_SUM_3_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_CREDITCARDWITHDRAWAL_SUM_0_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_CREDITCARDWITHDRAWAL_SUM_1_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_CREDITCARDWITHDRAWAL_SUM_2_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_CREDITCARDWITHDRAWAL_SUM_3_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_CREDITINCASH_SUM_0_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_CREDITINCASH_SUM_2_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_CREDITINCASH_SUM_3_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_REMITTANCETOANOTHERBANK_SUM_0_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_REMITTANCETOANOTHERBANK_SUM_1_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_REMITTANCETOANOTHERBANK_SUM_2_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_REMITTANCETOANOTHERBANK_SUM_3_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_WITHDRAWALINCASH_SUM_1_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_WITHDRAWALINCASH_SUM_2_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_WITHDRAWALINCASH_SUM_3_AMOUNT’;’QuarterlyTransaction_4Q4B_SUM_0_AMOUNT’;’QuarterlyTransaction_4Q4B_SUM_1_AMOUNT’;’QuarterlyTransaction_4Q4B_SUM_2_AMOUNT’;’QuarterlyTransaction_4Q4B_SUM_3_AMOUNT’]

      Checking internal state.

      The apply engine has detected ’33’ discarded variables: [‘A10′;’A13′;’A2′;’A3′;’A4′;’A6′;’A8′;’A9′;’DATE’;’FREQUENCY’;’GENDER’;’QuarterlyTransaction_4Q4B_OPERATION_COLLECTIONFROMANOTHERBANK_SUM_0_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_COLLECTIONFROMANOTHERBANK_SUM_1_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_COLLECTIONFROMANOTHERBANK_SUM_2_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_COLLECTIONFROMANOTHERBANK_SUM_3_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_CREDITCARDWITHDRAWAL_SUM_0_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_CREDITCARDWITHDRAWAL_SUM_1_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_CREDITCARDWITHDRAWAL_SUM_2_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_CREDITCARDWITHDRAWAL_SUM_3_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_CREDITINCASH_SUM_0_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_CREDITINCASH_SUM_2_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_CREDITINCASH_SUM_3_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_REMITTANCETOANOTHERBANK_SUM_0_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_REMITTANCETOANOTHERBANK_SUM_1_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_REMITTANCETOANOTHERBANK_SUM_2_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_REMITTANCETOANOTHERBANK_SUM_3_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_WITHDRAWALINCASH_SUM_1_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_WITHDRAWALINCASH_SUM_2_AMOUNT’;’QuarterlyTransaction_4Q4B_OPERATION_WITHDRAWALINCASH_SUM_3_AMOUNT’;’QuarterlyTransaction_4Q4B_SUM_0_AMOUNT’;’QuarterlyTransaction_4Q4B_SUM_1_AMOUNT’;’QuarterlyTransaction_4Q4B_SUM_2_AMOUNT’;’QuarterlyTransaction_4Q4B_SUM_3_AMOUNT’]

      Beginning of applying model. Please wait…

      Some intermediate entities missing in script KX_1448171476213536TSP_BUYSCREDITCARDNEXTQUARTER.

      Some intermediate entities missing in script KX_1448171476213536TSP_BUYSCREDITCARDNEXTQUARTER.

      Some intermediate entities missing in script KX_1448171476213536TSP_BUYSCREDITCARDNEXTQUARTER.

      Cleaning intermediate entities of script KX_1448171476213536TSP_BUYSCREDITCARDNEXTQUARTER.

      Creating intermediate entities of script KX_1448171476213536TSP_BUYSCREDITCARDNEXTQUARTER.

      Message from ODBC: insert into KX_144817147520853645CDABB0 (select KXTEMPT1.KXID as KXID, to_timestamp(‘2014-11-05 12:00:00’) as KXTIMESTAMP, KXTEMPT1.CLIENT_ID as CLIENT_ID, KXTEMPT1.DISTRICT_ID as DISTRICT_ID, KXTEMPT1.A5 as A5, KXTEMPT1.A7 as A7, KXTEMPT1.A11 as A11, KXTEMPT1.A12 as A12, KXTEMPT1.A14 as A14, KXTEMPT1.A15 as A15, KXTEMPT1.A16 as A16, KXTEMPT1.DISP_ID as DISP_ID, KXTEMPT1.ACCOUNT_ID as ACCOUNT_ID, KXTEMPT1.TYPE as TYPE, KXTEMPT1.DISTRICT_ID_2 as DISTRICT_ID_2, (days_between(KXTEMPT1.DATE_BIRTH , to_timestamp(‘2014-11-05 12:00:00’)) / 3.6525e2) as AGE, CORRT2.Expr_22 as QUARTERLYTRANSACTION_4Q4B_OPERATION_WITHDRAWALINCASH_SUM_0_AMOUNT, CORRT1.Expr_28 as QUARTERLYTRANSACTION_4Q4B_OPERATION_CREDITINCASH_SUM_1_AMOUNT, KXTEMPT1.BUYSCREDITCARDINQUARTERAFTERTIMESTAMP_1Q100A_EXST_0_CARD_ID as BUYSCREDITCARDINQUARTERAFTERTIMESTAMP_1Q100A_EXST_0_CARD_ID from KX_1448171476211536_FROMMODEL____ANA_CUSTOMER__TSP_BUYSCREDITCARDNEXTQUARTER_1448171402 KXTEMPT1 left outer join (select row_number() over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0 order by I056450TRANSACTION.”DATE” asc) as rn,  KXTEMPT1.REFT3_KXRN0 as REFT3_KXRN0, I056450TRANSACTION.ACCOUNT_ID as JKey, sum(I056450TRANSACTION.AMOUNT) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_28 from KX_1448171476211536_FROMMODEL____ANA_CUSTOMER__TSP_BUYSCREDITCARDNEXTQUARTER_1448171402 KXTEMPT1,  I056450.”TRANSACTION”  I056450TRANSACTION where ((I056450TRANSACTION.ACCOUNT_ID = KXTEMPT1.ACCOUNT_ID) and ((I056450TRANSACTION.”DATE” >= add_months(to_timestamp(‘2014-11-05 12:00:00’) , -9)) and (I056450TRANSACTION.”DATE” < add_months(to_timestamp(‘2014-11-05 12:00:00′) , -6)) and I056450TRANSACTION.OPERATION in (N’CREDIT IN CASH’)))) CORRT1 on ((CORRT1.JKey = KXTEMPT1.ACCOUNT_ID) and ( KXTEMPT1.REFT3_KXRN0 = CORRT1.REFT3_KXRN0) and (CORRT1.rn = 1)) left outer join (select row_number() over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0 order by I056450TRANSACTION.”DATE” asc) as rn,  KXTEMPT1.REFT3_KXRN0 as REFT3_KXRN0, I056450TRANSACTION.ACCOUNT_ID as JKey, sum(I056450TRANSACTION.AMOUNT) over (partition by I056450TRANSACTION.ACCOUNT_ID,  KXTEMPT1.REFT3_KXRN0) as Expr_22 from KX_1448171476211536_FROMMODEL____ANA_CUSTOMER__TSP_BUYSCREDITCARDNEXTQUARTER_1448171402 KXTEMPT1,  I056450.”TRANSACTION”  I056450TRANSACTION where ((I056450TRANSACTION.ACCOUNT_ID = KXTEMPT1.ACCOUNT_ID) and ((I056450TRANSACTION.”DATE” >= add_months(to_timestamp(‘2014-11-05 12:00:00’) , -12)) and (I056450TRANSACTION.”DATE” < add_months(to_timestamp(‘2014-11-05 12:00:00′) , -9)) and I056450TRANSACTION.OPERATION in (N’WITHDRAWAL IN CASH’)))) CORRT2 on ((CORRT2.JKey = KXTEMPT1.ACCOUNT_ID) and ( KXTEMPT1.REFT3_KXRN0 = CORRT2.REFT3_KXRN0) and (CORRT2.rn = 1))): [S1000][SAP AG][LIBODBCHDB DLL][HDBODBC] General error;287 cannot insert NULL or update to NULL: TrexUpdate failed on table ‘VENKAT:KX_144817147520853645CDABB0’ with error: constraint NOT NULL violation;checkNotNullColumn(): failed on pos 0,column ‘DISTRICT_ID_2’, table ‘VENKAT:KX_144817147520853645CDABB0’, rc=56

      Cannot load data into table KX_144817147520853645CDABB0.

      Delete objects created after successful execution of script KX_1448171476213536TSP_BUYSCREDITCARDNEXTQUARTER.

      Cleaning intermediate entities of script KX_1448171476213536TSP_BUYSCREDITCARDNEXTQUARTER.

      The dataset MyScores does not exist.

      The optimized in-database application process has failed.

      The standard application process will be used instead.

      Some intermediate entities missing in script KX_1448171485234536_FROMMODEL____ANA_CUSTOMER__TSP_BUYSCREDITCARDNEXTQUARTER_1448171402.

      Cleaning intermediate entities of script KX_1448171485234536_FROMMODEL____ANA_CUSTOMER__TSP_BUYSCREDITCARDNEXTQUARTER_1448171402.

      Creating intermediate entities of script KX_1448171485234536_FROMMODEL____ANA_CUSTOMER__TSP_BUYSCREDITCARDNEXTQUARTER_1448171402.

      Failure when creating an object in script KX_1448171485234536_FROMMODEL____ANA_CUSTOMER__TSP_BUYSCREDITCARDNEXTQUARTER_1448171402.

      Message from ODBC: SQLExecDirect: [42S02][SAP AG][LIBODBCHDB DLL][HDBODBC] Base table or view not found;259 invalid table name:  Could not find table/view KX_1448171484232536TSP_BUYSCREDITCARDNEXTQUARTER in schema VENKAT: line 1 col 1170 (at pos 1169)

      null

      End of Model Applying

      The error is showing the table is not creating on the fly in SAP HANA.  Could you please look into this.

      Thanks & Regards,

      Ramana.

      (0) 
      1. Andreas Forster Post author

        Hello Ramana,

        I think this is a user rights issue, your user might not be allowed to create a temporary table that is needed during the process.

        Let me see if I can get hold of a list of necessary rights. In the meantime, is it possible to test with with a SYSTEM user?

        Many Greetings

        Andreas

        (0) 
  3. Sorin Pascu

    Hi Andreas,

    We too need assistance with authorizations: for uploading the data with „Option 1: Import SAP HANA Content“, it seems authorizations to create schemas are needed – can you tell us which role it is in HANA?

    Thanks,

    Regards,

    Sorin

    (0) 
  4. Jan Fetzer

    Hi Andreas,

    on loading the transactional data (table “I056450″.”TRANSACTION”), HANA eventually throws an error:

    20160701_Table_TRANSACTION_fails_in_HANA.PNG

    All other tables load like a charm. Any idea? Or can you perhaps provide the transactional data in some other way (CSV?)

    Thx, Jan

    PS: Never mind, now I managed uploading the original data from PKDD as described in your chapter on “loading data manually”. All fine now.

    (0) 
    1. Andreas Forster Post author

      Hello Jan, page 67 in the tutorial explains how to manually load the data (from flat file).

      Maybe the term TRANSACTION has become a reserved word in a recent HANA version.

      Many Greetings

      Andreas

      (0) 
  5. Mister Makmerphy

    Hi

    Just for your info, your SQL mdf file is corrupted, most likely some uncommitted transactions. it was better to attach .bak file instead of mdf, mdf quite often are pain 

    (0) 
    1. Andreas Forster Post author

      Hi, I have updated the download link. Click on “Data (for SQL Server)” in the article and you get a new .mdf file. Time stamps in that database have been updated. When you go through the tutorial, please use 2016-08-05 as reference date when prompted in Data Manager.

      (0) 

Leave a Reply