Load, join, WHERE

[#1]

Hi,

I need to LOAD new records ONLY:

WHERE TimeStamp>= vUpdateDate

However, since I first have to make a join in order to get the TimeStamp and complete my table I cannot use a regular WHERE statement, or can I?

Any ideas how to work around this? Can I create a "Final Table" including the TimeStamp and then use a WHERE statement?

Im lost

Thanks in advance,

Olle 

NoConcatenate
LOAD CCY,
           ACC,
           BIC,
           ValueDate

FROM $(vSourceData)*.xls
(biff, embedded labels, header is 3 lines, table is @1)
WHERE Len(currency)=3;

DateTimeInfo:
LOAD
     Timestamp#((DATE(@1, 'MM/DD/YYYY')&' '&TIME(@2, 'hh:mm:ss')),'DD/MM/YYYY hh:mm:ss') As TimeStamp
FROM $(vSourceData)*.xls
(biff, no labels, header is 1 lines, table is @1) Where RecNo() < 2;

JOIN(Incremental)
LOAD * RESIDENT DateTimeInfo;
DROP TABLE DateTimeInfo;

[simondachstr]

I'm missing the following in your code:

• Declaration of vUpdateDate
• Where is the Incremental table?

Theoretically you should be able to do a final resident loada using a where statement. Something like:

LOAD *

Resident Incremental

WHERE TimeStamp >= '$(vUpdateDate)'

[amit_saini]

Olle,

Check if this helps u:

only want to load new records only...

Thanks,
AS

[amit_saini]

Also incremental load will help here:

Incremental Load

Thanks,

AS

[alkesh_sharma]

Load the incremental data and store it into a new qvd.

Load the new qvd into a test file and check the data is pulled as per your requirement.

Concatenate the new pulled data into the old qvd.

[#6]

Hi Martin,

sorry, should be as per below:

Kind Regards,

Olle

// ----QVD LOAD---- //

LOAD
    CCY,
    ACC,
    BIC,
    ValueDate,
    REF
FROM
$(vSaveQVD)final.qvd
(qvd);

Table2:
LOAD *
Resident Table1
Order By TimeStamp;
LET vUpdateDate = Peek('TimeStamp', -1, 'Table2');
Drop Table Table1;

//---- INCREMENTAL LOAD ----//

Incremental:

NoConcatenate
LOAD CCY,
          ACC,
          BIC,
          ValueDate,
          REF

FROM $(vSourceData)*.xls
(biff, embedded labels, header is 3 lines, table is @1)
WHERE Len(currency)=3;

DateTimeInfo:
LOAD
    Timestamp#((DATE(@1, 'MM/DD/YYYY')&' '&TIME(@2, 'hh:mm:ss')),'DD/MM/YYYY hh:mm:ss') As TimeStamp
FROM $(vSourceData)*.xls
(biff, no labels, header is 1 lines, table is @1) Where RecNo() < 2;

JOIN(Incremental)
LOAD * RESIDENT DateTimeInfo;
DROP TABLE DateTimeInfo;

//-- UPDATE MAIN QVD --//

Concatenate
LOAD
    CCY,
    ACC,
    BIC,
    ValueDate
FROM
$(vSaveQVD)SEBIS.qvd
(qvd)
WHERE NOT Exists(REF);
STORE Incremental into $(vSaveQvd)final.qvd(qvd);
Drop Table Incremental;

[simondachstr]

First of all be aware of the not exists issue and check if it applies to you.

http://community.qlik.com/docs/DOC-7020

Secondly try adding the following code before updating the main qvd

Final_Incremental

LOAD *

Resident Incremental

WHERE TimeStamp >= '$(vUpdateDate)'

Drop Tables Incremental;

[#8]

Hi Martin,

thanks for the reading tips! I think the exists function will work in my case.

So I added the "Final_Incremental" table into the Incremental Load section and replaced "Incremental" with "Final_Incremental" in the MAIN QVD UPDATE section. Please see script.

However, receiving below error message:

Table not found

STORE Final_Incremental into final.qvd(qvd)

Any ideas?

Kind Regards,

Olle

//---- INCREMENTAL LOAD ----//

Incremental:

NoConcatenate
LOAD CCY,
          ACC,
          BIC,
          ValueDate,
          REF

FROM $(vSourceData)*.xls
(biff, embedded labels, header is 3 lines, table is @1)
WHERE Len(currency)=3;

DateTimeInfo:
LOAD
    Timestamp#((DATE(@1, 'MM/DD/YYYY')&' '&TIME(@2, 'hh:mm:ss')),'DD/MM/YYYY hh:mm:ss') As TimeStamp
FROM $(vSourceData)*.xls
(biff, no labels, header is 1 lines, table is @1) Where RecNo() < 2;

JOIN(Incremental)
LOAD * RESIDENT DateTimeInfo;
DROP TABLE DateTimeInfo;

Final_Incremental:

LOAD *
Resident Incremental
WHERE TimeStamp >= '$(vUpdateDate)';
Drop Table Incremental;

//-- UPDATE MAIN QVD --//

Concatenate
LOAD
    CCY,
    ACC,
    BIC,
    ValueDate
FROM
$(vSaveQVD)SEBIS.qvd
(qvd)
WHERE NOT Exists(REF);
STORE Final_Incremental into $(vSaveQvd)final.qvd(qvd);
Drop Table Final_Incremental;

[simondachstr]

Sorry - that's my mystake.

Add a NoConcatenate statement before loading the Final_Incremental table

[#10]

No more error messages,

however the join does not seem to work (no TimeStamp field in the app) and getting synthetical keys on every field.
A tricky one!