First Value in the table

[surendraj]

Hi All,

in the below table, i need to capture the first value of the price for every model and month_year in a new field. (need in script level)

For Example:

for a model , if we have price for july2017 then we consider that price . if  july2017 has no price then we capture the price for aug2017  and so on ..

[MK_QSL]

Provide sample data for this.

[stabben23]

Hi,

maybe you can load it like follow.

MinPrice:

LOAD

Model,

min(Month_Year) as Min_MonthYear,

firstsortedvalue(Price, Month_Year) as Min_Price

Resident Your table

Group by Model;

Then Connect(join) on Model

EDITED!

[sunny_talwar]

May be Left Join like this

Left Join ( TableName)

LOAD Category,

     Crawler,

     Manufacturer,

     Model,

     FirstSortedValue(Price, - Month_Year) as LatestPrice

Resident TableName

Group By Category, Crawler, Manufacturer, Model;

[surendraj]

when i execute this .. no data in Min_Price. .

[surendraj]

after exciting this, no data in the latestprice .

[sunny_talwar]

How about if you do this

Left Join ( TableName)

LOAD Category,

    Crawler,

    Manufacturer,

    Model,

    FirstSortedValue(DISTINCT Price, - Month_Year) as LatestPrice

Resident TableName

Group By Category, Crawler, Manufacturer, Model;

[surendraj]

Output should be

[surendraj]

Output should be

[sunny_talwar]

Oh, you want the Price at the Min Date... try this

Left Join (TableName)

LOAD Category,

    Crawler,

    Manufacturer,

    Model,

    FirstSortedValue(DISTINCT Price, Month_Year) as LatestPrice

Resident TableName

Where Len(Trim(Price)) > 0

Group By Category, Crawler, Manufacturer, Model;