Concatenate QVD file with resident load

[Anonymous]

Hi Qlikview experts,

I have a requirement where I need to append new data to the existing QVD file(s).  Initial run when creating the QVD file also includes resident load for certain calculation.

When appending with new raw data I will also need to perform resident load and append the latest data to the existing QVD file and save it to latest QVD file.

While I was able to run the script the new data does not include any new result with resident load.

What I did was

Table_Name:

LOAD *

FROM

Filename.qvd (qvd);

concatenate

LOAD distinct

FROM

Filename.csv

left join

LOAD

Field Names

Resident Table Name

where xxxx (condition)

ORDER by filed name;

store Table_Name into new_table_name.qvd(qvd);

Any input or suggestion?

[sunny_talwar]

What you are asking for is Incremental reload. Check here: https://www.analyticsvidhya.com/blog/2014/09/qlikview-incremental-load/

[Anonymous]

Hi Sunny,

Thanks, I was actually able to append the new records. The only problem

that I have is the result of the calculation in the resident load is not

appended to the QVD, but all new records are appended.

How do I get the new calculated values with the new records are also

appended?

[swuehl]

There are multiple possible reasons why your code does not work as intended.

Maybe the JOIN keys don't show matching values, or your WHERE clause filters out all records.

Hard to tell looking at your pseudo code.

[Anonymous]

Actually I just simply copied the resident load part from the initial Load

and where clause filter is also the same with this initial run.

I can the new records are appended, but not the new values in the resident

load part.

Can the calculation with new records be appended in Qlikview?

On Wed, Oct 5, 2016 at 5:19 PM, Stefan Wühl <qcwebmaster@qlikview.com>

[swuehl]

Seems I am missing what you are trying to achieve with the LEFT JOIN of your resident LOAD (if it's just a copy of the initial load and where clause is the same, what's the goal here?).

Would be good to see a more realistic script snippet and some sample lines of data.

[Anonymous]

Hi,

Perhaps my description was not very clear. Here is more detail information. Hope it helps

I have data in csv files that consists of date, time and other fields, let say C, D, E, F. The file name keep adding on daily basis.

In the initial run when creating QVD file for the first month I did this:

Set vSourceData= 'Source file directory';

Set vQVDSave ='QVD Save directory';

Status:

Load Distinct

  RecNo() as ID,

  rep_date,

  Date (rep_date_date,'DD-MMM-YYYY') as Date,

  Month(rep_date) as Month,

     rep_time,

     area_id as Area,

     customer as [Customer Name],

     device_id as Device,

     Timestamp(Floor( Timestamp(rep_date & ' ' & rep_time,'DD/MM/YYYY hh:mm:ss'),1/288)) as [Time Stamp],

FROM

'Source Directory\*.csv'

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

where criteria;

left join

switch:

     LOAD

       [Time Stamp], Date,

       [Area], [Customer Name],

      if([Customer Name] = peek('Customer Name') and [Area] <> peek('Area'),1,0) as switch

     Resident Status 

     Where wildmatch(criteria)

     ORDER BY [Customer Name],[Time Stamp];

Last_Record:

LOAD Max ([Time Stamp]) as Latest_Time_Stamp

Resident Status;

LET Last_Record= Peek(('Latest_Time_Stamp'), 0, 'Last_Record');

trace >>> $(Last_Record); 

Table2:

LOAD

[Device],

Commercial

FROM

'data source.xls'

(biff, embedded labels, table is Sheet1$);

Table 3:

LOAD

@2 as Unit, @3 as Sector , @15 as [Id],

mid(@12,10,7)  as [Device id]

FROM

'Source.csv'

(txt, codepage is 1252, no labels, delimiter is ',', msq);

store Status into $(vQVDSave)Initial_status. qvd(qvd);

This runs well and I got all the records I need in QVD file.

In the the next run, what I did was like this:

Status:

LOAD

All fields in QVD file

FROM QVD file.qvd (qvd);

concatenate

Load Distinct

  RecNo() as ID,

  rep_date,

  Date (rep_date_date,'DD-MMM-YYYY') as Date,

  Month(rep_date) as Month,

     rep_time,

     area_id as Area,

     customer as [Customer Name],

     device_id as Device,

     Timestamp(Floor( Timestamp(rep_date & ' ' & rep_time,'DD/MM/YYYY hh:mm:ss'),1/288)) as [Time Stamp],

FROM

'Source Directory\*.csv'

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

where [Time Stamp] > >= '$(Last_Record)'

left join

//switch:

     LOAD

       [Time Stamp], Date,

       [Area], [Customer Name],

      if([Customer Name] = peek('Customer Name') and [Area] <> peek('Area'),1,0) as switch

     Resident Status 

     Where wildmatch(criteria)

     ORDER BY [Customer Name],[Time Stamp];

Last_Record:

LOAD Max ([Time Stamp]) as Latest_Time_Stamp

Resident Status;

LET Last_Record= Peek(('Latest_Time_Stamp'), 0, 'Last_Record');

trace >>> $(Last_Record); 

Table2:

LOAD

[Device],

Commercial

FROM

'data source.xls'

(biff, embedded labels, table is Sheet1$);

Table 3:

LOAD

@2 as Unit, @3 as Sector , @15 as [Id],

mid(@12,10,7)  as [Device id]

FROM

'Source.csv'

(txt, codepage is 1252, no labels, delimiter is ',', msq);

store Status into $(vQVDSave)Updated_status. qvd(qvd);

The next run I just use the latest qvd file to append with the new records. New records are appended but  not this one.

switch:

     LOAD

       [Time Stamp], Date,

       [Area], [Customer Name],

      if([Customer Name] = peek('Customer Name') and [Area] <> peek('Area'),1,0) as switch

     Resident Status 

     Where wildmatch(criteria)

     ORDER BY [Customer Name],[Time Stamp];

I hope this is clear enough.

Thanks for help

[swuehl]

[switch] field is already part of table Status, isn't it?

If yes, it's considered a key field and I don't think that's what you want here.

Besides that, what is your criteria you are using in the WHERE clause?

[Anonymous]

Yes, [Switch] field is already part of the table status in QVD file and that is a result of the calculation in the previous run, not in the new records. 

Actually, I did  not make a separate table for that switch. It was made as comment //switch before join statement.

This field [switch] is not the csv file, so  I am assuming I have to do another calculation every time I downloaded new records  and put the results to the same field [switch] and then append them to the QVD file that has values from the previous records and calculation.  All other fields seems appended but not this [switch] field.

The criteria that I used in the second run (concatenating QVD with new csv files) is the same as when QVD was initially created. I copied that criteria from initial run.

It is something like this:

Where wildmatch(device, '*CX*'), 'IN', 'OUT')

Anything missing from my code?

[Anonymous]

I was finally able to get it work with this approach:

Table1:

LOAD

FROM

QVD file.qvd (qvd);

Last Record:

LOAD Max ([Time Stamp]) as Latest_Time_Stamp

Resident Table1:;

  LET Last_Record= Peek(('Latest_Time_Stamp'), 0, 'Last_Record');

trace >>> $(Last_Record);

Drop Table 1;

Updated_Status:

LOAD

[Fields in csv file]

FROM

csvfile.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

where [Time Stamp] > '('Last_Record')'

Left join

LOAD

[Some fields]

calculation statement

Resident Updated_Status

where xxx

order by

// the above is also part of calculation in the initial load - Please see previous posts

concatenate

[Fields in QVD file]

//same fields with csv file

FROM

QVD file.qvd (qvd);

LOAD Table 2

FROM

csvfile.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

// Note this join and the result was also part of the initial load when creating initial qvd file

Store Updated Status into Updated_status.qvd(qvd);

I also want to get the latest time stamp from the latest updated file so that I can show this field also. In this case it seems I need to add another statement before storing the file to latest qvd file. Something like this:

Last Record:

LOAD Max ([Time Stamp]) as Latest_Time_Stamp

Resident Updated_Status

  LET Last_Record= Peek(('Latest_Time_Stamp'), 0, 'Last_Record');

trace >>> $(Last_Record);

The question that I have is why do I have to drop the table first and reload them again to make it work?  Since I have over 12 million records to concatenate, it makes much longer time to complete this process and I feel it is inefficient.

Could someone explain to me about this and is there a better way to efficiently do this?