topic Re: First Value in the table in Archived Groups

First Value in the table

surendraj — Tue, 22 Jul 2025 17:59:49 GMT

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 ..

Re: First Value in the table

MK_QSL — Fri, 13 Apr 2018 11:04:11 GMT

Provide sample data for this.

Re: First Value in the table

stabben23 — Fri, 13 Apr 2018 11:13:15 GMT

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!

Re: First Value in the table

sunny_talwar — Fri, 13 Apr 2018 11:17:27 GMT

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;

Re: First Value in the table

surendraj — Fri, 13 Apr 2018 12:28:57 GMT

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

Re: First Value in the table

surendraj — Fri, 13 Apr 2018 12:32:21 GMT

after exciting this, no data in the latestprice .

Re: First Value in the table

sunny_talwar — Fri, 13 Apr 2018 12:33:14 GMT

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;

Re: First Value in the table

surendraj — Fri, 13 Apr 2018 12:36:06 GMT

Output should be

Re: First Value in the table

surendraj — Fri, 13 Apr 2018 12:37:03 GMT

Output should be

Re: First Value in the table

sunny_talwar — Fri, 13 Apr 2018 12:39:14 GMT

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;

Re: First Value in the table

stabben23 — Fri, 13 Apr 2018 12:44:53 GMT

should work, send Excel instead of pic?

Re: First Value in the table

stabben23 — Fri, 13 Apr 2018 12:46:40 GMT

maybe you have to format min(Month_Year) With date?

Re: First Value in the table

surendraj — Fri, 13 Apr 2018 12:47:21 GMT

not getting in data in latest price field.

Re: First Value in the table

sunny_talwar — Fri, 13 Apr 2018 12:48:49 GMT

I think stabben23‌ brought up a good point, is your Month_Year understood as date by QlikView?

Why don’t my dates work?

QlikView Date fields

Get the Dates Right

Re: First Value in the table

surendraj — Fri, 13 Apr 2018 12:51:56 GMT

from data field .. i created month_year field

Re: First Value in the table

stabben23 — Fri, 13 Apr 2018 12:55:49 GMT

looks lika a string(text) value

Re: First Value in the table

surendraj — Fri, 13 Apr 2018 12:59:46 GMT

after removing the month_year and replacing it with monthlastday field. Below is the data we see .

In the lastestprice field, the value is begin halfed .

Re: First Value in the table

sunny_talwar — Fri, 13 Apr 2018 13:01:34 GMT

Are you using an expression for Price? or is it direct field reference?

Re: First Value in the table

surendraj — Fri, 13 Apr 2018 13:03:03 GMT

Price is direct field ...

Re: First Value in the table

surendraj — Fri, 13 Apr 2018 13:04:10 GMT

No Data in the latestprice