How to store dynamically changing value

[Anonymous]

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  jagan‌hic‌Robert_Mika‌stevedark‌

[marcus_sommer · Accepted solution]

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

[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

[Anonymous]

it is working but to be frank i didnt get the logic ,and synthetic key is forming here

[marcus_sommer]

If you run at first this:

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);

and later then without the comments (of course this could be handled with a if-condition) you get synthetic keys?

One hint to them because it might not notice, the concatenation from the qvd should be happens once after the for-loop is finished and not each time within the loop.

- Marcus

[Anonymous]

yup got it thanks for the quick response