Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

qliklearnervir
New Contributor III

Top 1 Record from Minimum Created Date

Hi,

I have 1 requirement where i want to pull Top 1 record from minimum created date .

Columns are given below:

Prospect:

Person_wid,

ACQ_Date,

ACQ_Source,

Created_Date

I wan to pull 1 row for each Person_wid with Applying logic for Top 1 record from minimum created date.

Please suggest.

6 Replies

Re: Top 1 Record from Minimum Created Date

you can use firstSortedValue()

Regards,

Regards,
Prashant Sangle
qliklearnervir
New Contributor III

Re: Top 1 Record from Minimum Created Date

Can you give me a written script...as i have never used it before...

Re: Top 1 Record from Minimum Created Date

Hi,

below example from help menu

Temp:

LOAD * inline [

Customer|Product|OrderNumber|UnitSales|CustomerID

Astrida|AA|1|10|1

Astrida|AA|7|18|1

Astrida|BB|4|9|1

Astrida|CC|6|2|1

Betacab|AA|5|4|2

Betacab|BB|2|5|2

Betacab|DD|12|25|2

Canutility|AA|3|8|3

Canutility|CC|13|19|3

Divadip|AA|9|16|4

Divadip|AA|10|16|4

Divadip|DD|11|10|4

] (delimiter is '|');

FirstSortedValue:

LOAD Customer,FirstSortedValue(Product, UnitSales) as MyProductWithSmallestOrderByCustomer Resident Temp Group By Customer;

Here there are calculating Customer purchasing product at  lowest price.

Regards,

Regards,
Prashant Sangle

Re: Top 1 Record from Minimum Created Date

Prospect:

Person_wid,

ACQ_Date,

ACQ_Source,

Created_Date

FROM table;

left join(Prospect)

LOAD Person_wid,

           Firstsortedvalue(distinct ACQ_Source, Created_Date) as ACQ_Source_Min

Resident Prospect

group by Prospect;

qliklearnervir
New Contributor III

Re: Top 1 Record from Minimum Created Date

HOw we can perfrom group by using table name  like group by Prospect;

Re: Top 1 Record from Minimum Created Date

it's typo

Prospect:

Person_wid,

ACQ_Date,

ACQ_Source,

Created_Date

FROM table;

left join(Prospect)

LOAD Person_wid,

           Firstsortedvalue(distinct ACQ_Source, Created_Date) as ACQ_Source_Min

Resident Prospect

group by Person_wid;

Community Browser