5 Replies Latest reply: Apr 6, 2016 9:41 AM by qvhelp please RSS

    How to store dynamically changing value

    qvhelp please

      Hello Pros

       

      i am trying to caluclate the difference of size of qvd before and after the incremental load i am using the filesize() function .

       

      Script looks like this:

      Temp_Files:

      LOAD

           FileName

      INLINE [

      FileName

      C:\Users\Documents\samp2.qvd

       

       

      ];

       

       

      for i = 0 to NoOfRows('Temp_Files') -1

           let vFile = Peek('FileName', i, 'Temp_Files');

           let vFileSize = Alt(FileSize('$(vFile)'), 0);

       

           FileSize:

             LOAD

                '$(vFile)' as FileName,

                $(vFileSize) as FileSize

           AUTOGENERATE(1)

           ;

      next

       

       

      DROP TABLE Temp_Files;

       

       

      But what happening is when i reload the file after the incremental load the value gets override which leaves we with no value for comparison. can any one please suggest a way through this? i think we need to generate the unique field every time but not sure  jaganhicrobert_mikastevedark

        • Re: How to store dynamically changing value
          Marcus Sommer

          I think if you want beside the current size see the development of changes you should add a date (and maybe a time) to the your FileSize table and store it as qvd and the next time you concatenate these qvd to the current FileSize:

           

          FileSize:

                 LOAD

                    '$(vFile)' as FileName,

                    $(vFileSize) as FileSize,

                    today() as Date

               AUTOGENERATE(1);

           

               concatenate(FileSize)

               Load * From FileSize.qvd (qvd);

               store FileSize into FileSize.qvd (qvd);

           

          - Marcus

          • Re: How to store dynamically changing value
            Amit Saini

            Hi,

             

            Try something like below:

             

            // Delta or Initial Load

             

            if not IsNull(QvdCreateTime('$(vQVD)\ABC.qvd')) then

               

                // delta load: read from existing qvd header

                SET vLoadType_M = 'delta';

               

                // 1. Get AEDAT and CPUDT

                GetDate:

                LOAD AEDAT, CPUDT

                FROM $(vQVD)\ABC.qvd (qvd);

             

                // 2. Concatenate both dates in one column (e.g. to prevent null values)   

                ConDate:

                LOAD AEDAT as DAT

                Resident GetDate;

               

                LOAD CPUDT as DAT

                Resident GetDate;

               

                // 3. Get the last date

                MaxDate:

                LOAD max(DAT) as MaxDAT

                Resident ConDate

                Group by 1;

               

               

                LET vLastLoad_M = Date(Peek('MaxDAT', 0, 'MaxDate'), 'YYYYMMDD');

                TRACE DELTALOAD for ABC & XYZ, starting at $(vLastLoad_M);

             

                DROP Tables GetDate, ConDate, MaxDate;

               

            ELSE

                // initial load

               

               

             

                SET vLoadType_M = 'initial';

                SET vLastLoad_M = '20120101';        // to be defined---------------------------------------------

               

                Let vStartYear    = Num(Year(Date#('$(vLastLoad_M)','YYYYMMDD')));                        // Set Min Start Year

                LET vEndYear    = num(Year(Today()));                                                    // Set Max Start Year

               

                YearString:                                                                               

                LOAD Concat(Year_,chr(39) & ',' & Chr(39))    as LoopYear;                                // Generate String for For each Loop if using initial Load

                Load

                '$(vStartYear)' + RecNo()-1         as Year_

                AutoGenerate(vEndYear - vStartYear +1);

             

             

                Let vYearLoopInitial    = chr(39) & Peek('LoopYear',-1,'YearString') & chr(39);         // Pick String for For each Loop if using initial Load

                DROP Table YearString;

             

                TRACE INITIALLOAD for ABC & XYZ, starting at $(vLastLoad_M) for Years $(vYearLoopInitial);

             

            ENDIF

             

             

            if vLoadType_M = 'delta' then

             

            ABC:

            LOAD

                *,

                MBLNR &'_'& MJAHR as ABC_Key;

            SQL SELECT

                *

            FROM ABC

            WHERE AEDAT GE '$(vLastLoad_M)' or CPUDT GE '$(vLastLoad_M)'

            ;

             

             

                // add records from previous load:

                concatenate(ABC)

                load * from ABC.qvd(qvd)

                // but exclude updated records:

                where not exists (ABC_Key);

               

                STORE ABC into ABC.qvd;

                DROP Table ABC;

             

            else

             

            FOR Each vLoopYear in $(vYearLoopInitial)  // Split ABC in Years only for reduce amaount of Datasets during initial load

             

                ABC_$(vLoopYear):

                LOAD

                    *,

                    MBLNR &'_'& MJAHR as ABC_Key;

                    SQL SELECT

                    *

                    FROM ABC where MJAHR = $(vLoopYear);

               

             

               

                STORE ABC_$(vLoopYear) into ABC_$(vLoopYear).qvd;

                DROP Table ABC_$(vLoopYear);

               

                NEXT

               

                ABC:

                Load * from ABC_*.qvd (qvd);                                    // Concatenate all generated yearly ABC Files to one

                STORE ABC into ABC.qvd (qvd);

                DROP Table ABC;

             

            endif

             

             

            IF vLoadType_M = 'delta' THEN

             

            XYZ:

            LOAD

                *,

                MBLNR &'_'& ZEILE as XYZ_Key;

            SQL SUBSELECT 

            FROM XYZ

            WHERE MBLNR MJAHR IN (

                SELECT MBLNR MJAHR FROM ABC WHERE AEDAT GE '$(vLastLoad_M)' or CPUDT GE '$(vLastLoad_M)'

            );

             

             

                // add records from previous load:

                concatenate(XYZ)

                load * from XYZ.qvd(qvd)

                // but exclude updated records:

                where not exists (XYZ_Key);

               

                STORE XYZ into XYZ.qvd;

                DROP Table XYZ;

             

            ELSE

             

             

                FOR Each vLoopYear in $(vYearLoopInitial) // Split XYZ in Years only for reduce amount of Datasets during initial load

               

                XYZ_$(vLoopYear):

                LOAD

                    *,

                    MBLNR &'_'& ZEILE as XYZ_Key;

                SQL  

                    *

                FROM XYZ where MJAHR = $(vLoopYear);

               

               

                STORE XYZ_$(vLoopYear) into XYZ_$(vLoopYear).qvd;

                DROP Table XYZ_$(vLoopYear);

               

                NEXT

             

                XYZ:

                Load * from XYZ_*.qvd (qvd);                            // Concatenate all generated yearly XYZ Files to one

                STORE XYZ into XYZ.qvd (qvd);

                DROP Table XYZ;

             

            Thanks,

            AS