Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
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.

1 Solution

Accepted Solutions
aveeeeeee7en
Specialist III
Specialist III

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

View solution in original post

4 Replies
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

Great dreamer's dreams never fulfilled, they are always transcended.
marcus_sommer
MVP & Luminary
MVP & Luminary

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;

Thanks & Regards,
Mayil Vahanan R
aveeeeeee7en
Specialist III
Specialist III

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

View solution in original post