Hands-On Tutorial SAP Predictive Analytics, Automated Mode: Data Manager
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.
thanks, great work 😀
Indeed - very nice piece! Thanks Andreas!
Great! Thanks for posting!
Great work!
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
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?
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.
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
next time please add this as an attachment, do not be that dumb!
Thanks for document.
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
Hi Sorin,
I got to admit I am no expert in HANA authorizations, but there is a system privilege "CREATE SCHEMA", maybe this helps.
https://help.sap.com/saphelp_hanaplatform/helpdata/en/20/f674e1751910148a8b990d33efbdc5/content.htm
If this does not allow you to import the content, maybe you want to ask the question in the HANA forum on SCN.
Many Greetings
Andreas
Hi Andreas,
on loading the transactional data (table "I056450"."TRANSACTION"), HANA eventually throws an error:
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.
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
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
Hello Mister Makmrerphy, I'll mention this to Andreas. Thanks Antoine
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.