topic Re: How to store dynamically changing value in QlikView

How to store dynamically changing value

Anonymous — Wed, 06 Apr 2016 09:06:58 GMT

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)

;

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‌

Re: How to store dynamically changing value

marcus_sommer — Wed, 06 Apr 2016 11:23:04 GMT

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 — Wed, 06 Apr 2016 11:27:56 GMT

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

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

XYZ:

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

STORE XYZ into XYZ.qvd (qvd);

DROP Table XYZ;

Thanks,

Re: How to store dynamically changing value

Anonymous — Wed, 06 Apr 2016 12:39:11 GMT

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

Re: How to store dynamically changing value

marcus_sommer — Wed, 06 Apr 2016 12:49:12 GMT

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

Re: How to store dynamically changing value

Anonymous — Wed, 06 Apr 2016 13:41:29 GMT

yup got it thanks for the quick response