load new data from last reload

[Anonymous]

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,

[alex_millan]

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

[stigchel]

Maybe you can just use something like

where not exists (TrackId,TrackId);

See also this thread:

Re: Conditional load based on an incremental load

[Anonymous]

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

[alex_millan]

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...

[Anonymous]

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

[Anonymous]

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.

[alex_millan]

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

[Anonymous]

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

Thanks,

[alex_millan]

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.