topic Re: Top 1 Record from Minimum Created Date in QlikView

Top 1 Record from Minimum Created Date

qliklearnervir — Wed, 19 Jul 2017 05:52:08 GMT

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.

Re: Top 1 Record from Minimum Created Date

PrashantSangle — Wed, 19 Jul 2017 05:54:17 GMT

you can use firstSortedValue()

Regards,

Re: Top 1 Record from Minimum Created Date

qliklearnervir — Wed, 19 Jul 2017 06:02:45 GMT

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

Re: Top 1 Record from Minimum Created Date

PrashantSangle — Wed, 19 Jul 2017 06:14:26 GMT

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,

Re: Top 1 Record from Minimum Created Date

Kushal_Chawda — Wed, 19 Jul 2017 07:04:13 GMT

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;

Re: Top 1 Record from Minimum Created Date

qliklearnervir — Thu, 20 Jul 2017 06:48:11 GMT

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

Re: Top 1 Record from Minimum Created Date

Kushal_Chawda — Thu, 20 Jul 2017 07:17:52 GMT

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;