4 Replies Latest reply: Jun 19, 2018 8:20 AM by Avinash R RSS

    How to load Max(Date) and Max(Date)-1 Qvds

    Mahitha M

      Hi Experts,

       

      Can any one please help me on below requirement.

       

      In C:/Qliksharefolder/4.QVDs path I have number of qvds with date like below.

       

      kensitivities_Detail_20180520.qvd

      Kensitivities_Detail_20180521.qvd

      Kensitivities_Detail_20180616.qvd

      Kensitivities_Detail_20180617.qvd


      From these qvds I have to load only Max(date) and Max(date)-1 qvds i.e.,

      Kensitivities_Detail_20180616.qvd

      Kensitivities_Detail_20180617.qvd

      dynamically in the load script


      Please help me on this.


      Thanks in advance.


        • Re: How to load Max(Date) and Max(Date)-1 Qvds
          Rangam Seshadri

          try this

           

          load

          Max(date) as Mdate,

          Max(date)-1 as Mdate_1

          from [C:/Qliksharefolder/kensitivities_Detail*.qvd](qvd);


          In case, if you want to consider date from file name then use below script:


          date:

          LOAD


          date(date#(max(mid(FileName(),Index(FileName(),'Detail_')+7,8)),'YYYYMMDD'),'MM/DD/YYYY') as Date

          FROM [C:/Qliksharefolder/kensitivities_Detail*.qvd] (qvd);


          MaxDate:

          load text(date(max(Date),'YYYYMMDD')) as MDate,

          text(date(Max(Date)-1,'YYYYMMDD')) as MDate_1 Resident date;


          let Vmaxdate=Peek('MDate',-1, 'MaxDate');

          let Vmaxdate_1=Peek('MDate_1',-1, 'MaxDate');


          Data1:

          LOAD

          *

          FROM [C:/Qliksharefolder/kensitivities_Detail_$(Vmaxdate).qvd] (qvd);




          LOAD

          *

          FROM [C:/Qliksharefolder/kensitivities_Detail_$(Vmaxdate_1).qvd] (qvd);



          • Re: How to load Max(Date) and Max(Date)-1 Qvds
            Avinash R

            Try like this

             

            Temp:

            Load Date(Date#(Right(FileName(),8),'YYYYMMDD'),'YYYYMMDD') as DateField as Temp_Date

            from [C:/Qliksharefolder/kensitivities_Detail_*.qvd] (qvd)

            ;

            Date:

            Load Temp_Date as Date,

            Resident Temp

            Order By

            Temp_Date

            ;

             

            LET vMaxDate = Peek('Date',-1,'Date');

            LET vSecMaxDate = Peek('Date',-1,'Date');


            DATA:

            LOAD

            *

            from [C:/Qliksharefolder/kensitivities_Detail_'$(vMaxDate)'.qvd] (qvd);


            LOAD

            *

            from [C:/Qliksharefolder/kensitivities_Detail_'$(vSecMaxDate )'.qvd] (qvd);


              • Re: How to load Max(Date) and Max(Date)-1 Qvds
                Mahitha M

                Hi Avinash,

                 

                Thanks for your reply.

                The above script is not working. Not getting the date records in the Temp_Date field.

                  • Re: How to load Max(Date) and Max(Date)-1 Qvds
                    Avinash R

                    Sorry my bad


                    Temp:

                    Load

                    //add any one field from the QVD, I simply assumed it as A

                    A,

                    Date(Date#(Right(FileBaseName(),8),'YYYYMMDD'),'YYYYMMDD') as Temp_Date

                    from [C:/Qliksharefolder/kensitivities_Detail_*.qvd] (qvd)

                    ;

                    Date:

                    Load Temp_Date as Date

                    Resident Temp

                    Order By

                    Temp_Date

                    ;

                    Date:

                     

                    Load Temp_Date as Date

                     

                    Resident Temp

                    Order By

                    Temp_Date

                    ;

                     

                     

                     

                     

                    DROP Table Temp;

                     

                     

                     

                    LET vMaxDate = Peek('Date',-1,'Date');

                     

                     

                    LET vSecMaxDate = Peek('Date',-2,'Date');

                     

                     

                    DATA:

                     

                    LOAD

                    *

                    from [C:/Qliksharefolder/kensitivities_Detail_$(vMaxDate).qvd] (qvd);

                     

                    LOAD

                    *

                    from [C:/Qliksharefolder/kensitivities_Detail_$(vSecMaxDate ).qvd] (qvd);