Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
it is working but to be frank i didnt get the logic ,and synthetic key is forming here
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
yup got it thanks for the quick response