4 Replies Latest reply: Sep 17, 2014 2:46 AM by AVIRAL NAG RSS

    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.

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

          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

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

            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
              mayilvahanan ramasamy

              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;

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

                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