topic Re: Load, join, WHERE in QlikView

Load, join, WHERE

Wed, 03 Dec 2014 11:18:06 GMT

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;

Re: Load, join, WHERE

simondachstr — Wed, 03 Dec 2014 11:21:26 GMT

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

Re: Load, join, WHERE

amit_saini — Wed, 03 Dec 2014 11:23:26 GMT

Olle,

Check if this helps u:

only want to load new records only...

Thanks,
AS

Re: Load, join, WHERE

amit_saini — Wed, 03 Dec 2014 11:26:32 GMT

Also incremental load will help here:

Incremental Load

Thanks,

Re: Load, join, WHERE

alkesh_sharma — Wed, 03 Dec 2014 11:31:37 GMT

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.

Re: Re: Load, join, WHERE

Wed, 03 Dec 2014 11:36:00 GMT

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;

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;

Re: Re: Load, join, WHERE

simondachstr — Wed, 03 Dec 2014 11:42:26 GMT

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;

Re: Re: Re: Load, join, WHERE

Wed, 03 Dec 2014 12:58:10 GMT

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;

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;

Re: Re: Re: Load, join, WHERE

simondachstr — Wed, 03 Dec 2014 13:01:52 GMT

Sorry - that's my mystake.

Add a NoConcatenate statement before loading the Final_Incremental table

Re: Re: Re: Load, join, WHERE

Wed, 03 Dec 2014 13:37:54 GMT

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!

Re: Re: Re: Load, join, WHERE

simondachstr — Wed, 03 Dec 2014 13:40:47 GMT

For this I would need a sample of your whole loading script

Make sure $(vUpdateDate) is evaluated properly.

Add a TRACE '$(vUpdateDate)'; and check the script log to make sure it does.

Re: Re: Re: Re: Load, join, WHERE

Wed, 03 Dec 2014 16:21:04 GMT

Hi Martin,

I checked $(vUpdateDate) and it looked ok.

Please find attached a sample file with my entire load script.

Also, I've assembled 3 sample reports to load data from.

As mentioned before, there's synthetic key issues.

Thanks in advance!

Kind Regards,

Olle

Re: Load, join, WHERE

Fri, 05 Dec 2014 10:27:28 GMT

Hi Martin,

hope you are well!

Did you have a chance to have a look at my script yet?

Any help would me much appreciated!

Kind Regards,

Olle

Re: Re: Load, join, WHERE

simondachstr — Sun, 07 Dec 2014 19:28:24 GMT

Apologies for the late reply!

See attached. There were a couple of syntax errors (always end your trace statement with a ; ). I've tested the script by creating a new Report4.xls and changing the date to T+1 and the incremental load worked. Make sure you change your vSourceData and vSaveQVD variables back to your File-Path as I've modifed them in the script.

Let me know if it works okay for you.

Re: Re: Load, join, WHERE

Wed, 10 Dec 2014 08:48:52 GMT

Works great, thanks a lot Martin!

Kind regards,

Olle