Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
vidyasagar159
Contributor II

Unable to Load Max Year data

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)
;

Labels (1)
3 Replies

Re: Unable to Load Max Year data

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
Contributor II

Re: Unable to Load Max Year data

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

 

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

 

 

Thanks,

-Vidya

vidyasagar159
Contributor II

Re: Unable to Load Max Year data

Thanks Sunny.