Denorm Examples | Vantage Analytics Library - Denorm Examples - Vantage Analytics Library

Call:

call td_analyze(
	'denormalize','
	database=val_source;
	tablename=accounts;
	indexretaincolumns=cust_id;
	indexremovecolumns=acct_type;
	valuestodenorm=CC,CK,SV;
	denormalizecolumns=ending_balance;
');

SQL:

SELECT
	 "cust_id"
	,MIN(CASE WHEN "acct_type" = 'CC' THEN "ending_balance" ELSE NULL END) AS "CC_ending_balance"
	,MIN(CASE WHEN "acct_type" = 'CK' THEN "ending_balance" ELSE NULL END) AS "CK_ending_balance"
	,MIN(CASE WHEN "acct_type" = 'SV' THEN "ending_balance" ELSE NULL END) AS "SV_ending_balance"

FROM "val_source"." accounts"
GROUP BY "cust_id"
;

Results:

Call:

call td_analyze(
	'denormalize','
	database=val_source;
	tablename= checking_tran;
	indexretaincolumns=cust_id;
	indexremovecolumns=channel;
	valuestodenorm=A,B,C;
	denormalizecolumns=new_balance;
	aggregation=count;
');

SQL:

SELECT
	 "cust_id"
	,COUNT(CASE WHEN "channel" = 'CC' THEN "new_balance" ELSE NULL END) AS "CC_new_balance"
	,COUNT(CASE WHEN "channel" = 'CK' THEN "new_balance" ELSE NULL END) AS "CK_new_balance"
	,COUNT(CASE WHEN "channel" = 'SV' THEN "new_balance" ELSE NULL END) AS "SV_new_balance"

FROM "val_source"."checking_tran"
GROUP BY "cust_id"
;

Results:

The following table shows the results when the aggregation method is changed from COUNT to AVG:

In the following example, a retaincolumns column is specified to include the column in the denormalized output table. To demonstrate this, it is first necessary to build a test table from the standard tutorial tables (in this case, the accounts table in the val_results database):

DROP TABLE "val_results"."_val_accounts2";
CREATE SET TABLE "val_results"."_val_accounts2" AS (
SELECT T1.*, 'abc' AS abc
FROM "val_source"."accounts" T1
) WITH DATA PRIMARY INDEX("acct_nbr");

Call:

call td_analyze(
	'denormalize','
	database=val_results;
	tablename=_val_accounts2;
	indexretaincolumns=cust_id;
	indexremovecolumns=acct_type;
	denormalizecolumns=ending_balance;
	retaincolumns=abc;
');

SQL:

SELECT
	 "cust_id"
	,"abc"
	,MIN(CASE WHEN "acct_type" = 'CC' THEN "ending_balance" ELSE NULL END) AS "CC_ending_balance"
	,MIN(CASE WHEN "acct_type" = 'CK' THEN "ending_balance" ELSE NULL END) AS "CK_ending_balance"
	,MIN(CASE WHEN "acct_type" = 'SV' THEN "ending_balance" ELSE NULL END) AS "SV_ending_balance"

FROM "val_results"."_val_accounts2"
GROUP BY "cust_id", "abc"
;

Results: