Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

First and last record of an article in a table

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.

Tags (3)
1 Solution

Accepted Solutions
aveeeeeee7en
Valued Contributor III

Re: First and last record of an article in a table

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:

All Scenarios.png

2) For 1 Case Article Name - 4711:

One Scenario.png

Also, see the Attachment.

Hope that helps you.

Regards

Aviral Nag

4 Replies

Re: First and last record of an article in a table

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

Great dreamer's dreams never fulfilled, they are always transcended.

Re: First and last record of an article in a table

Try this:

MinMaxDate:

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

- Marcus

Re: First and last record of an article in a table

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
Valued Contributor III

Re: First and last record of an article in a table

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:

All Scenarios.png

2) For 1 Case Article Name - 4711:

One Scenario.png

Also, see the Attachment.

Hope that helps you.

Regards

Aviral Nag

Community Browser