topic Re: load new data from last reload in QlikView

load new data from last reload

Anonymous — Wed, 15 Jul 2015 15:34:22 GMT

hi guys,

I have SQL script, where I get respondentID and key variable "TrackId" which I load every morning.

Then I have another query where I load data based on "TrackId". Then I store the second data to qvd.

The problem is the second query has more "TrackId" value.

If I use max(TrackId) from qvd file, I skip some numbers.

Is it possible, when I load the first query, store last or maximum value of "TrackId" and then use this variable to load new data from the second query.

The new max variable should be from my last reload, not today.

Any ideas?

Thanks,

Re: load new data from last reload

alex_millan — Wed, 15 Jul 2015 15:47:26 GMT

Hi Vladimir, perhaps this example may help you:

Temp: LOAD min(TrackID) as MinTrackId, max(TrackID) as MaxTrackId Resident YourTable;

Let vMinTrackId = Num(Peek('MinTrackId', 0, 'Temp'));

Let vMaxTrackId = Num(Peek('MaxTrackId', 0, 'Temp'));

Then you use these variables.

HTH

Regards

Re: load new data from last reload

stigchel — Wed, 15 Jul 2015 15:50:52 GMT

Maybe you can just use something like

where not exists (TrackId,TrackId);

Re: load new data from last reload

Anonymous — Wed, 15 Jul 2015 16:17:33 GMT

I think it won't work for me, because when I reload new data each morning vMaxTrackId will be updated on new data, not on previous reload.

For example yesterday vMaxTrackId was 1452366, after new reload vMaxTrackId will be 1479899. I lost some numbers.

Qvd file has more data in TrackID field. I did like you suggest, but miss some numbers.Because 2 database used different time stamp main use EDT and the second use GTM.

Temp: LOAD min(TrackID) as MinTrackId, max(TrackID) as MaxTrackId Resident YourTable;

Let vMinTrackId = Num(Peek('MinTrackId', 0, 'Temp'));

Let vMaxTrackId = Num(Peek('MaxTrackId', 0, 'Temp'));

If I will use Resident qvd, my TrackId is bigger than main (first) query

If I will use Resident fist query, my TrackId is updated based on last reload

I need to define max value TrackID from previous reload in first query

Re: load new data from last reload

alex_millan — Wed, 15 Jul 2015 16:29:50 GMT

Perhaps we may go for another approach, perhaps you may store those daily values on a table to be able to use them later.

Sounds a bit odd for me too but if it may work...

Re: load new data from last reload

Anonymous — Wed, 15 Jul 2015 18:03:30 GMT

Thanks for reply.

How works condition

where not exists (TrackId,TrackId);

my screenshots

from main query I take TrackID

from the second query I load only that info which match by Id and UserTrackID

140902159 is the last number for yesterday reload. when I use vmaxTrackID from qvd file, the number is bigger than in my main query.

Id I called paramValue.

USERTRACKID:

SQL SELECT paramValue, respondentID

FROM cRespondentData3rdInfo WHERE surveyID=$(surveyID) and isnumeric(paramValue)=1

and paramName='UserTrackID' and paramValue>$(vMaxUserTrackId);

TempTable:

Load

Concat(Distinct paramValue,',' ) as ConcatValue

Resident

USERTRACKID;

Let vValue=FieldValue('ConcatValue',1);

Drop Table TempTable;

COMMENT_URLs:

SQL SELECT [Id] as paramValue

,[SessionId]

,[TimeStamp]

,[Ip]

,[Url]

FROM [SEAC].[dbo].[UserTrack] where Id in ($(vValue));

TempTable:

Load

Chr(39) & Concat(Distinct SessionId,Chr(39) & ',' & Chr(39)) &Chr(39) as ConcatValue

Resident

COMMENT_URLs;

Let vValue2=FieldValue('ConcatValue',1);

Drop Table TempTable;

SESSIONS:

SQL SELECT

[SessionId]

,[TimeStamp]

,[Ip]

,[Url]

,[Id] as paramValue

FROM [SEAC].[dbo].[UserTrack] where SessionId in ($(vValue2))

ORDER BY SessionId, [TimeStamp] ASC;

CONCATENATE

LOAD DISTINCT * FROM (qvd);

// WHERE NOT EXISTS(paramValue);

I used condition not exists, then there is no link between 2 database. When I call UserTrackId =140902159 from main, there is no link to his sessionID

Re: load new data from last reload

Anonymous — Wed, 15 Jul 2015 19:14:57 GMT

I probably found out how to do it.

I changed order of loading

the second query loading the first and then the main

When I load the main query and create temp with vMaxTrackId.

Now I got other problem

if there is no new data, I got a message

USERTRACKID:

SQL SELECT paramValue, respondentID

FROM cRespondentData3rdInfo WHERE surveyID=$(surveyID) and isnumeric(paramValue)=1

and paramName='UserTrackID' and paramValue>$(vMaxUserTrackId);

TempTable:

Load

Concat(Distinct paramValue,',' ) as ConcatValue

Resident

USERTRACKID;

Let vValue=FieldValue('ConcatValue',1);

Drop Table TempTable;

COMMENT_URLs:

SQL SELECT [Id] as paramValue

,[SessionId]

,[TimeStamp]

,[Ip]

,[Url]

FROM [SEAC].[dbo].[UserTrack] where Id in ($(vValue));

CONCATENATE

LOAD DISTINCT * FROM (qvd);

What condition can I add if there is no new data, just load qvd.

Re: load new data from last reload

alex_millan — Wed, 15 Jul 2015 19:31:36 GMT

If you want to check out if there are new records to load, perhaps a preliminar count would it serve for this purpose.

Re: load new data from last reload

Anonymous — Wed, 15 Jul 2015 19:45:26 GMT

I've never used it. Could you show how?

Thanks,

Re: load new data from last reload

alex_millan — Wed, 15 Jul 2015 20:02:13 GMT

Me neither, just giving a suggestion, but it you´re sure this error is caused by the non existence of new records, you can make a select count first and condition the load transaction that is giving you problems only when this count is greater than zero.

Anyway, in your screen shot it seems the cause is an incorrect syntax, could it be? are you sure this where clause at the end of the sql transaction works well? Is the variable vValue taking a value?

Perhaps you can check this out by using the debug mode in running the script. Just to rest assured.

Re: load new data from last reload

Anonymous — Wed, 15 Jul 2015 20:08:52 GMT

it works well, if there is Id grater than vmaxTrackId.

Re: load new data from last reload

alex_millan — Thu, 16 Jul 2015 09:50:53 GMT

Hi Vladimir,

Seems to me that this error occurs when vValue is empty (at least in the screenshot seems to be empty).

If so, perhaps you can implement an if before loading your 'COMMENT_URLs' table.

If len('$(vValue)') > 0

Then

COMMENT_URLs:

SQL SELECT [Id] as paramValue, [SessionId], [TimeStamp], [Ip], [Url]

FROM [SEAC].[dbo].[UserTrack] where Id in ($(vValue));

End if

I'm not sure if it would work but perhaps it's worth to try.

Good luck

Re: load new data from last reload

alex_millan — Mon, 20 Jul 2015 12:32:11 GMT

Hi Vladimir,

did you finally manage to solve it?

Regards,

Re: load new data from last reload

Anonymous — Tue, 21 Jul 2015 14:56:27 GMT

I have an idea how it might work.

From my main file after each reload I create vMaxUserTrackId in Temp table. (last keyID from the main file).

Then I added new temp table to find vLastNumber, which take only 1 max(keyID) from the main file

SELECT TOP 1 paramValue FROM [cRespondentData] as cRD ...

....

Let vLastNumber = Num(Peek('paramValue', 0, 'MaxValue'));

Here I'm stuck

I need a condition

IF $(vMaxUserTrackId)>$(vLastNumber)

THEN

should be load all scripts

ELSE should be load only my qvd file.

When I added to my QV scripts the condition below

IF $(vMaxUserTrackId)>$(vLastNumber)

THEN

USERTRACKID:

SQL SELECT paramValue, respondentID

FROM cRespondentData3rdInfo WHERE surveyID=$(surveyID) and isnumeric(paramValue)=1

and paramName='UserTrackID' and paramValue>$(vMaxUserTrackId);

Starting from THEN QV reads like wrong expression.

Any suggestion, I'd be appreciated.

Thank you again,

Re: load new data from last reload

Anonymous — Tue, 21 Jul 2015 15:09:15 GMT

I found out where the problem is

IF $(vMaxUserTrackId)>$(vLastNumber) THEN

IF and then should be in 1 row

but when I load only qvd file

LOAD AutoNumber(paramValue) as Auto_Id,*

FROM (qvd)

ORDER BY SessionId, [TimeStamp] ASC;

It works, but gave me script error

how can I avoid it?

Re: load new data from last reload

Anonymous — Tue, 21 Jul 2015 15:16:59 GMT

It looks like qvd file can't use order by.

I used resident load