topic Using Max functon in Script in QlikView

Using Max functon in Script

Tue, 23 Aug 2011 05:19:19 GMT

Dear All,

I want to execute a line of code in Script please suggest me how can i do it,

My Code to put in Script is

if(PriceDate_CargoRisk = max(PriceDate_CargoRisk),ExposureDuration_CargoRisk) as TD_ExposureDuration_CargoRisk,

But it gives error while reloading please can any one help me

Regards,

Pranav

Using Max functon in Script

Tue, 23 Aug 2011 05:42:43 GMT

What is the error?

What else have you tried?

Can you provide a qvw with an example?

Using Max functon in Script

Tue, 23 Aug 2011 05:48:54 GMT

Dear Kieran,

What is the error?

Ans. The erroe it shows while reload in Script as Invalid Expression.

What else have you tried?

Ans. Now i have made it like this

NCCargoRisk:

InputField SpotBnkPrice_Input;

LOAD NCCargoRisk.NumberOfDays as TotNrOfDays_CargoRisk,

NCCargoRisk.ID as MTMID_CargoRisk,

NCCargoRisk.ID as NCCargoRisk_Link_CargoRiskResident,

NCCargoRisk.TCEQSECONDLEG as TCEQSecondLeg_CargoRisk,

NCCargoRisk.SECONDLEGPRICE as SecondLegPrice_CargoRisk,

NCCargoRisk.CARGONAME as CargoName_CargoRisk,

Date(NCCargoRisk.STARTDATE,'$(DateFormat)') as StartDate_CargoRisk,

Date(NCCargoRisk.ENDDATE,'$(DateFormat)') as EndDate_CargoRisk,

NCCargoRisk.QUANTITY as Qty_CargoRisk,

NCCargoRisk.DURATIONSECONDLEG as DurationSecondLeg_CargoRisk,

NCCargoRisk.FREIGHTRATE as FreightRate_CargoRisk,

Date(NCCargoRisk.PRICEDATE,'$(DateFormat)') as PriceDate_CargoRisk,

NCCargoRisk.IFOPRICE as IFOPrice_CargoRisk,

-(NCCargoRisk.Duration_Risk) as ExposureDuration_CargoRisk,

NCCargoRisk.MarketValue_Risk as MarketValue_CargoRisk

FROM [$(QVDPATHTRANS)NCCargoRisk.qvd] (qvd);

left join

Load Max(PriceDate_CargoRisk,'$(DateFormat)') as PriceDate_CargoRisk1 resident NCCargoRisk;

CargoResident:

LOAD MTMID_CargoRisk,

PriceDate_CargoRisk as PriceDate_CargoRisk_Resident,

//MaxString(PriceDate_CargoRisk) as Max_PriceDate,

if(PriceDate_CargoRisk = PriceDate_CargoRisk1,ExposureDuration_CargoRisk) as TD_ExposureDuration_CargoRisk,

ExposureDuration_CargoRisk as ED_CargoRisk_Today

resident NCCargoRisk;

Its Working Fine now

Re: Using Max functon in Script

Tue, 23 Aug 2011 06:11:23 GMT

max( expression[, rank] )
Returns the maximum numeric value of expression encountered over a number of records as defined by a group by clause. Rank defaults to 1 which corresponds to the highest value. By specifying rank as 2 the second highest value will be returned. If rank is 3 the third highest value will be returned and so on.
Example:
Load Month, max(Sales) as LargestSalePerMonth from abc.csv group by Month;
Load Month, max(Sales, 2) as SecondLargestSalePerMonth from abc.csv group by Month;

I down totally understand your idea but try this

left join
Load date(Max(PriceDate_CargoRisk),'$(DateFormat)') as PriceDate_CargoRisk1 
resident NCCargoRisk

This will add PriceDate_CargoRisk1 field for every line in table NCCargoRisk

Using Max functon in Script

Tue, 23 Aug 2011 07:08:21 GMT

Hi alex,

Thanks for your answers it was helpful, if i want now to get the previous day date then how to do it

Regards,

Pranav

Using Max functon in Script

Tue, 23 Aug 2011 07:11:25 GMT

Hey Alex got the answer for prev days just to write :

left join Load date(Max(PriceDate_CargoRisk),'$(DateFormat)') as PriceDate_CargoRisk1,

date(Max((PriceDate_CargoRisk)-1),'$(DateFormat)') as PriceDate_CargoRisk1
resident NCCargoRisk