Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Please help. For some reason resident table is not loading. I tried many options nothing seems to be working finally I came up with the below script still no luck. I have a similar logic applied before which is working fine the only difference in the below concatenation is i am using RowNo() as a Rank.
OutPut:
Script:
/////////////////////////////////////////////////////////Merging All AUM and NetNewFlow Target Tables ////////////////////////////////////
[100MillionNNFTable]:
Load Distinct
RowNo() as TRank,
"Manager Name" as "TManagerName",
"Morningstar Category" as "TMorningstarCategory",
"Inception Date" AS "TInception Date",
"Index Fund" as "TIndexFund",
"Product Name" as "TProductName",
"UProducts" as "TUniqueProducts",
"Asset Class" as "TAssetClass",
MetricsFlag as TMetricsFlag,
DataSource as TDataSource,
[Year]as TYear,
[Year]-Year("Inception Date") as TProductAge
//Metrics as TMetrics
Resident FinaTable
Where
[Year]-Year("Inception Date") =0
and
Metrics <= 100
and
MetricsFlag = 'Net New Flow'
Order by Metrics desc;
Test1:
Load * Resident [100MillionNNFTable];
Drop Table [100MillionNNFTable];
[100MillionAUMTable]:
Load Distinct
RowNo() as TRank,
"Manager Name" as "TManagerName",
"Morningstar Category" as "TMorningstarCategory",
"Inception Date" AS "TInception Date",
"Index Fund" as "TIndexFund",
"Product Name" as "TProductName",
"UProducts" as "TUniqueProducts",
"Asset Class" as "TAssetClass",
MetricsFlag as TMetricsFlag,
DataSource as TDataSource,
[Year]as TYear,
[Year]-Year("Inception Date") as TProductAge
//Metrics as TMetrics
Resident FinaTable
Where
[Year]-Year("Inception Date") =0
and
Metrics <= 100
and
MetricsFlag = 'AUM'
Order by Metrics desc
;
Test2:
Load * Resident [100MillionAUMTable];
Drop Table [100MillionAUMTable];
[100MillionTable]:
NoConcatenate
load * Resident Test1;
Concatenate
load * Resident Test2;
drop Tables Test1,Test2;
Script:
/////////////////////////////////////////////////////////Merging All AUM and NetNewFlow Target Tables ////////////////////////////////////
[100MillionNNFTable]:
Load Distinct
RowNo() as TRank,
"Manager Name" as "TManagerName",
"Morningstar Category" as "TMorningstarCategory",
"Inception Date" AS "TInception Date",
"Index Fund" as "TIndexFund",
"Product Name" as "TProductName",
"UProducts" as "TUniqueProducts",
"Asset Class" as "TAssetClass",
MetricsFlag as TMetricsFlag,
DataSource as TDataSource,
[Year]as TYear,
[Year]-Year("Inception Date") as TProductAge
//Metrics as TMetrics
Resident FinaTable
Where
[Year]-Year("Inception Date") =0
and
Metrics <= 100
and
MetricsFlag = 'Net New Flow'
Order by Metrics desc;
Noconcatenate
Test1:
Load * Resident [100MillionNNFTable];
Drop Table [100MillionNNFTable];
[100MillionAUMTable]:
Load Distinct
RowNo() as TRank,
"Manager Name" as "TManagerName",
"Morningstar Category" as "TMorningstarCategory",
"Inception Date" AS "TInception Date",
"Index Fund" as "TIndexFund",
"Product Name" as "TProductName",
"UProducts" as "TUniqueProducts",
"Asset Class" as "TAssetClass",
MetricsFlag as TMetricsFlag,
DataSource as TDataSource,
[Year]as TYear,
[Year]-Year("Inception Date") as TProductAge
//Metrics as TMetrics
Resident FinaTable
Where
[Year]-Year("Inception Date") =0
and
Metrics <= 100
and
MetricsFlag = 'AUM'
Order by Metrics desc
;
Noconcatenate
Test2:
Load * Resident [100MillionAUMTable];
Drop Table [100MillionAUMTable];
[100MillionTable]:
NoConcatenate
load * Resident Test1;
Concatenate
load * Resident Test2;
drop Tables Test1,Test2;
Try this.
Script:
/////////////////////////////////////////////////////////Merging All AUM and NetNewFlow Target Tables ////////////////////////////////////
[100MillionNNFTable]:
Load Distinct
RowNo() as TRank,
"Manager Name" as "TManagerName",
"Morningstar Category" as "TMorningstarCategory",
"Inception Date" AS "TInception Date",
"Index Fund" as "TIndexFund",
"Product Name" as "TProductName",
"UProducts" as "TUniqueProducts",
"Asset Class" as "TAssetClass",
MetricsFlag as TMetricsFlag,
DataSource as TDataSource,
[Year]as TYear,
[Year]-Year("Inception Date") as TProductAge
//Metrics as TMetrics
Resident FinaTable
Where
[Year]-Year("Inception Date") =0
and
Metrics <= 100
and
MetricsFlag = 'Net New Flow'
Order by Metrics desc;
Test1:
Load *,1 as Flag Resident [100MillionNNFTable];
Drop Table [100MillionNNFTable];
[100MillionAUMTable]:
Load Distinct
RowNo() as TRank,
"Manager Name" as "TManagerName",
"Morningstar Category" as "TMorningstarCategory",
"Inception Date" AS "TInception Date",
"Index Fund" as "TIndexFund",
"Product Name" as "TProductName",
"UProducts" as "TUniqueProducts",
"Asset Class" as "TAssetClass",
MetricsFlag as TMetricsFlag,
DataSource as TDataSource,
[Year]as TYear,
[Year]-Year("Inception Date") as TProductAge
//Metrics as TMetrics
Resident FinaTable
Where
[Year]-Year("Inception Date") =0
and
Metrics <= 100
and
MetricsFlag = 'AUM'
Order by Metrics desc
;
Test2:
Load * , 2 as Flag Resident [100MillionAUMTable];
Drop Table [100MillionAUMTable];
[100MillionTable]:
NoConcatenate
load * Resident Test1;
Concatenate
load * Resident Test2;
drop Tables Test1,Test2;
Script:
/////////////////////////////////////////////////////////Merging All AUM and NetNewFlow Target Tables ////////////////////////////////////
[100MillionNNFTable]:
Load Distinct
RowNo() as TRank,
"Manager Name" as "TManagerName",
"Morningstar Category" as "TMorningstarCategory",
"Inception Date" AS "TInception Date",
"Index Fund" as "TIndexFund",
"Product Name" as "TProductName",
"UProducts" as "TUniqueProducts",
"Asset Class" as "TAssetClass",
MetricsFlag as TMetricsFlag,
DataSource as TDataSource,
[Year]as TYear,
[Year]-Year("Inception Date") as TProductAge
//Metrics as TMetrics
Resident FinaTable
Where
[Year]-Year("Inception Date") =0
and
Metrics <= 100
and
MetricsFlag = 'Net New Flow'
Order by Metrics desc;
Noconcatenate
Test1:
Load * Resident [100MillionNNFTable];
Drop Table [100MillionNNFTable];
[100MillionAUMTable]:
Load Distinct
RowNo() as TRank,
"Manager Name" as "TManagerName",
"Morningstar Category" as "TMorningstarCategory",
"Inception Date" AS "TInception Date",
"Index Fund" as "TIndexFund",
"Product Name" as "TProductName",
"UProducts" as "TUniqueProducts",
"Asset Class" as "TAssetClass",
MetricsFlag as TMetricsFlag,
DataSource as TDataSource,
[Year]as TYear,
[Year]-Year("Inception Date") as TProductAge
//Metrics as TMetrics
Resident FinaTable
Where
[Year]-Year("Inception Date") =0
and
Metrics <= 100
and
MetricsFlag = 'AUM'
Order by Metrics desc
;
Noconcatenate
Test2:
Load * Resident [100MillionAUMTable];
Drop Table [100MillionAUMTable];
[100MillionTable]:
NoConcatenate
load * Resident Test1;
Concatenate
load * Resident Test2;
drop Tables Test1,Test2;