Unable to Load Max Year data

[vidyasagar159]

Hi All,

Can some one please help me with script to load only MAX year data. Here is the script i used but not woking.

When I used variable i am getting 'Field 'a' not found' error.

TempFundAssetUS:
LOAD
max(year(PERDT)) as MaxYearlyAUM
FROM [lib://GPG_QVD/BrightScope\SI_AUM_US.qvd]
(qvd);

//Let vMin = peek('mini',0,'TMP');

let vMaxYear = MaxYearlyAUM;

//drop table TempFundAssetUS;

FundAssetUS:
LOAD
FNDNU as "Fund No",
NETASTUSDAM as [Asset Usd],
PERDT as "Period Date",
num(Month(PERDT))as "Monthly AUM",
year(PERDT) as "Yearly AUM",
PERNU as Period,
CUSIPNU as Cusip_nu,
FNDNM as "Fund Name",
MRNCTGNM as "MorningStar Category",
TKRCD as Ticker,
IDXFNDDSCTX as "Index Fund",
If(IDXFNDDSCTX = 'No','Active','Passive') as "Active and Passive Aseet Funds",
MGRCMYNM as "Manager Name"
FROM [lib://GPG_QVD/BrightScope\SI_AUM_US.qvd]
(qvd)
where Year(PERDT)= $(vMaxYear)
;

[sunny_talwar]

Try this

TempFundAssetUS:
LOAD
max(year(PERDT)) as MaxYearlyAUM
FROM [lib://GPG_QVD/BrightScope\SI_AUM_US.qvd]
(qvd);

//Let vMin = peek('mini',0,'TMP');

let vMaxYear = Peek('MaxYearlyAUM');

//drop table TempFundAssetUS;

FundAssetUS:
LOAD
FNDNU as "Fund No",
NETASTUSDAM as [Asset Usd],
PERDT as "Period Date",
num(Month(PERDT))as "Monthly AUM",
year(PERDT) as "Yearly AUM",
PERNU as Period,
CUSIPNU as Cusip_nu,
FNDNM as "Fund Name",
MRNCTGNM as "MorningStar Category",
TKRCD as Ticker,
IDXFNDDSCTX as "Index Fund",
If(IDXFNDDSCTX = 'No','Active','Passive') as "Active and Passive Aseet Funds",
MGRCMYNM as "Manager Name"
FROM [lib://GPG_QVD/BrightScope\SI_AUM_US.qvd]
(qvd)
where Year(PERDT)= $(vMaxYear)
;

[vidyasagar159]

I got found the solution. All i need to do is modify variable.

let vMaxYear = peek('MaxYearlyAUM',0,'TempFundAssetUS');

Thanks,

-Vidya

[vidyasagar159]

Thanks Sunny.