First and last record of an article in a table

[#1]

Hi, i`ve started with qlikview and now i have a small problem.

Iám triying to built a new report, which we show me the oldest and the neweset record of one article.

How can i find these entrys.

The table is filled like this example.

Article/Article Name/price/qty/date

4710/nipple/1,5/3/01.02.2010

4711/ring/2/5/02.03.2009

4711/ring/2,1/10/05.07.2011

4711/ring/1,8/100/09.11.2013

4711/ring/2,1/2/11.11.2013

4711/ring/1,9/50/03.01.2013

4712/clit/25/4/01.01.2010

4712/clit/10/2/31.12.2010

and so on.

So what i need is an table where i want to see the oldest and the newest record of every Article.

Did somebody has an idea? I tried it with min max but i didn`t get the correct result.

[PrashantSangle]

Hi,

First Clean your data in load

Like

Subfield(Article/Article Name/price/qty/date,'/',1) as Article,

Subfield(Article/Article Name/price/qty/date,'/',2) as Article Name,

Subfield(Article/Article Name/price/qty/date,'/',3) as price,

Subfield(Article/Article Name/price/qty/date,'/',4) as qty,

Subfield(Article/Article Name/price/qty/date,'/',5) as date

From tablename;

Then use FirstSortedValue() either iin load or you can use it in Chart to acheive your desired result.

I hope you get the logic to start you work.

Regards

[marcus_sommer]

Try this:

MinMaxDate:

Load Article, min(date) as MinDate, max(date) as MaxDate From xyz group by Article;

- Marcus

[MayilVahanan]

Hi

Try like this

ArticleInfo :

LOAD Article,[Article Name],price,qty,date(Date#(date,'dd.MM.yyyy')) as date Inline
[
Article,Article Name,price,qty,date
4710,nipple,1.5,3,01.02.2010
4711,ring,2,5,02.03.2009
4711,ring,2.1,10,05.07.2011
4711,ring,1.8,100,09.11.2013
4711,ring,2.1,2,11.11.2013
4711,ring,1.9,50,03.01.2013
4712,clit,25,4,01.01.2010
4712,clit,10,2,31.12.2010
];
LOAD Article, LastValue(date) as LastDate, FirstValue(date) as FirstDate Resident ArticleInfo Group by Article order by date;

[aveeeeeee7en · Accepted solution]

Hi

Use FirstSortedValue() Function:

FirstSortedValue(date,-purgechar(date,'.'))  for Oldest Record

FirstSortedValue(date,-purgechar(date,'.'))  for Newest Record

Use this Script:

Table1:

Load *, Subfield(Name,'/',1) AS Article,

Subfield(Name,'/',2) AS Article_Name,

Subfield(Name,'/',3) AS price,

Subfield(Name,'/',4) AS qty,

Subfield(Name,'/',5) AS date INLINE [

Name

'4710/nipple/1,5/3/01.02.2010'

'4711/ring/2/5/02.03.2009'

'4711/ring/2,1/10/05.07.2011'

'4711/ring/1,8/100/09.11.2013'

'4711/ring/2,1/2/11.11.2013'

'4711/ring/1,9/50/03.01.2013'

'4712/clit/25/4/01.01.2010'

'4712/clit/10/2/31.12.2010'

];

See the Snapshots:

1) All cases:

2) For 1 Case Article Name - 4711:

Also, see the Attachment.

Hope that helps you.

Regards

Aviral Nag