8 Replies Latest reply: Feb 20, 2014 2:37 AM by Kalyana Sundaram RSS

    QVD Distribution according to Period

    Kalyana Sundaram

      Hi all,

       

      I face much more reloading time due to huge amount of data. We planned to implement Incremental Load. But it was not necessary for my client, because they are updating db three months once. 

       

       

      So I need to seperate the data from db as

      Table_201101-201310 -- History(I wont reload it once again)

      Table_ 201310-201402 -- (Reload this upto Feb end)

      ......vice versa

       

      we can use variable to proceed for the next period, but user have to change the period in Qlikview by changing the value in Input box.

      But this kind of method was not agreed by my client.

       

      So I planned to use notepad, where i could enter the period no. and use tat notepad in our script, i will provide that notepad to my client and ask them to change the period no. so tat we can get next period data automatically without changing our script.

       

      Is it possible to do using above plan? if yes, please help me how to do with the scripting!

       

      Thanks in Advance,

       

      Best Regards,

      Kalyan.D

        • Re: QVD Distribution according to Period
          Peter Cammaert

          Why does the user have to change the period? There are no user configurable reloads in QVS...

           

          Can this not be done automatically, as in loading everything from a fixed or sliding start date up until MonthEnd(today()) as an increment?

           

          Best,

           

          Peter

          • Re: QVD Distribution according to Period
            Fernando Tonial

            You try this:

             

            LET vMesInicial = Date('01/01/2014');
            LET vMesFinal     = Date(MonthEnd(Today())-2);
            
            DATES:
            LOAD
                Date(AddMonths('$(vMesInicial)',RowNo()-1),'YYYYMMDD')                 AS DiaIni,
                Date(MonthEnd(AddMonths('$(vMesInicial)',RowNo()-1)),'YYYYMMDD')     AS DiaFim,
                Date(AddMonths('$(vMesInicial)',RowNo()-1),'YYYYMM')                 AS AnoMes
            AutoGenerate(Ceil((vMesFinal-vMesInicial)/30.5));
            
            //EXIT Script;
            
            FOR D=1 TO (NoOfRows('DATES'))
            
                LET vAnoMes = Peek('AnoMes',$(D)-1);
                LET vDiaIni = Peek('DiaIni',$(D)-1);
                LET vDiaFim = Peek('DiaFim',$(D)-1);
               
                TRACE $(vAnoMes);
                [Table]:
                SQL Select * from Table where MonthYear = '$(vAnoMes)';
               
                Store [Table] into DirExtraidos\Table_$(vAnoMes).QVD;
                Drop table [Table];
               
            NEXT
            
            DROP Table DATES;
            
            

             

            Best Regards

            • Re: QVD Distribution according to Period
              Friedrich Hofmann

              Hi Kalyan,

               

              generally, I would always try to use as few distinct elements as possible - so I would suggest not using Notepad or any other external tool, but only the qvd files themselves.

              - So every qvd should hold five months' worth of data, yes?

              - I assume you already have created that archive_qvd that goes up to 201310, yes?

              => Then my suggestion would be this:

              - Find out how to load the file_names of all the files in the directory where those qvd files should be (unfortunately I don't know how to do that, but (not ideally, but alternatively) you might use your .txt approach there, as a qvs file to be populated by the customer and as a last resort you can always hard-code them in an Ínline table)

              - Get the largest second ("to") year-month figure from all of those files

              - Compare that to the current year-month

                   - If the current year-month is still within the scope of that file, you can use it
                     => the new data should be appended to that qvd

                  - If the current year-month is not in the scope of that file anymore, you have to create a new one
                    => First, LOAD RESIDENT (from some other table, doesn't matter) an empty table, just to have the fields

                    => then append the new data to that

              HTH

               

              Best regards,

               

              DataNibbler

              • Re: QVD Distribution according to Period
                Kalyana Sundaram

                Hi all,

                 

                I had tried my level best, just by filtering date in the table.

                I had created From and To notepad, in From, start date, in To end date,

                Asked user to modify the date once they are updating db three months once.

                I load tat notepad in QV, and set it as variable,

                ex:

                vFrom='$(Include.....);';

                vTo='$(Include);';

                 

                Then in where condtion

                 

                where Date>=$(vFrom) and Date<=$(vTo)

                 

                and store it as

                 

                Table_$(vFrom)-$(vTo).qvd

                 

                then

                 

                I take all the qvd by using * sign after the table name.

                EX;

                Load * From Table_*.qvd;

                 

                so this will read all the data from the beginning.

                 

                Thanks to everyone who had replied to my discussion.

                 

                Best Regards,

                Kalyan.D