load sql select where part not functioning

[#1]

The following script is not working like it should be.

I tried to change my loading to incremental loading.

But the moment my script should only load the ID's bigger than my variable it goes wrong.

He loads everything instead of only the ID's that are bigger.

And my variable is correctly filled.

I have the feeling he does not recognize my field : Work_Log_ID

Can anybody help me pls

here the script

WORKLOG:

LOAD
max(num(Right("Work_Log_ID",12))) as Work_Log_ID
From (qvd)
group by "Work_Log_ID";
// LET vMaxWorklogID = peek("Work_Log_ID", 0, WORKLOG);
let vMaxWorklogID =

FieldValue('Work_Log_ID',1);

// SET vMaxWorklogDate = timestamp(now());
// LET vWorklogQvdCreateDate = QvdCreateTime('C:\Users\m999kbr\Desktop\Worklog.qvd');
// LET vWorklogUpdateDate = num(timestamp('$(vWorklogQvdCreateDate)'));
// set vWorklogChangeDate = $(vWorklogUpdateDate);

DROP

Table WORKLOG;

WORKLOG:

LOAD

"Assign_WorkLog_Flag",

Description as WorklogDescription,

"Detailed_Description",

"Incident_Number" as CaseID,

num(Right("Work_Log_ID",12)) as Work_Log_ID,

"Work_Log_Submit_Date",

"Work_Log_Submitter",

"Work_Log_Type";

SQL

SELECT

"Assign_WorkLog_Flag",

Description,

"Detailed_Description",

"Incident_Number",

"Work_Log_ID",

"Work_Log_Submit_Date",

"Work_Log_Submitter",

"Work_Log_Type"

FROM "HPD_WorkLog"

Where (Work_Log_ID > '$(vMaxWorklogID)');

// UNQUALIFY *;
// WORKLOG:
// Concatenate LOAD *
// From (qvd)
// where not Exists('Work_Log_ID');
// STORE WORKLOG into Worklog.qvd(qvd);

[Colin-Albert]

Use PEEK to get vMaxWorklogID

let vMaxWorklogID = PEEK('Work_Log_ID', -1, 'WORKLOG');

[Anonymous]

Koen

Add

     Trace Value of vMaxWorklogID is  $(vMaxWorklogID)

Just after you set the variable so when you can run the script you can confirm what value it has.

Then run the SQL manually against your data but typing in displayed the Value of vMaxWorklogID to see what that returns.

Also to be more explicit i'd replace the second  WORKLOG: with Concatenate(WORKLOG)

Best Regards,     Bill

[Colin-Albert]

Also step through the script in Debug mode and you can view the SQL SELECT statement to see how the variable is being passed to SQL.

[#5]

ok i adapted it to peek as you mentioned but it still fetches all lines instead of just those bigger than vMaxWorklogID.

Here the adapted script

WORKLOG:

LOAD
max(num(Right("Work_Log_ID",12))) as Work_Log_ID
From (qvd)
group by "Work_Log_ID";

LET vMaxWorklogID = peek('Work_Log_ID', -1);

// let vMaxWorklogID = FieldValue('Work_Log_ID',1);
// SET vMaxWorklogDate = timestamp(now());
// LET vWorklogQvdCreateDate = QvdCreateTime('C:\Users\m999kbr\Desktop\Worklog.qvd');
// LET vWorklogUpdateDate = num(timestamp('$(vWorklogQvdCreateDate)'));
// set vWorklogChangeDate = $(vWorklogUpdateDate);

DROP

Table WORKLOG;

WORKLOG:

LOAD

"Assign_WorkLog_Flag",

Description as WorklogDescription,

"Detailed_Description",

"Incident_Number" as CaseID,

num(Right("Work_Log_ID",12)) as Work_Log_ID,

"Work_Log_Submit_Date",

"Work_Log_Submitter",

"Work_Log_Type";

SQL

SELECT

"Assign_WorkLog_Flag",

Description,

"Detailed_Description",

"Incident_Number",

"Work_Log_ID",

"Work_Log_Submit_Date",

"Work_Log_Submitter",

"Work_Log_Type"

FROM "HPD_WorkLog"

Where (Work_Log_ID>'$(vMaxWorklogID)');

// UNQUALIFY *;
// WORKLOG:
// Concatenate LOAD *
// From (qvd)
// where not Exists('Work_Log_ID');
// STORE WORKLOG into Worklog.qvd(qvd);

and here the script execution progress

Connected
WORKLOG << Worklog 275,608 lines fetched
WORKLOG << HPD_WorkLog 275,763 lines fetched

As you can see it should only fetch 155 lines the second fetch

[#6]

ok i added the trace and i see that the variable vMaxWorklogID is filled in with 195098.

its not a correct value cause if i look into table there are bigger ID's but at least it should only fetch lines from 195099 to 275763 and not all 275763.

Strange i start to understand even less now lol

[Anonymous]

In your first Load statement where you are trying to find the Max value, you have a group by the same Variable in which you are trying to find the MAX value for.  Try removing the Group By Work_Log_ID and see if that helps.

[#8]

ok gonna comment out all the rest and just concentrate first on finding correct max value.

worklogid example of data is WLG000000124574.

With num and rigth i keep 124574 which is correct in table.

here the script i use without group by.

value still wrong

WORKLOG:

LOAD
num(Right("Work_Log_ID",12)) as Work_Log_ID
From (qvd);
LET vMaxWorklogID = peek('Work_Log_ID', -1);

Trace Value of vMaxWorklogID is $(vMaxWorklogID);

// let vMaxWorklogID = FieldValue('Work_Log_ID',1);
// SET vMaxWorklogDate = timestamp(now());
// LET vWorklogQvdCreateDate = QvdCreateTime('C:\Users\m999kbr\Desktop\Worklog.qvd');
// LET vWorklogUpdateDate = num(timestamp('$(vWorklogQvdCreateDate)'));
// set vWorklogChangeDate = $(vWorklogUpdateDate);
// DROP Table WORKLOG;
//
//WORKLOG:
// LOAD
// "Assign_WorkLog_Flag",
// Description as WorklogDescription,
// "Detailed_Description",
// "Incident_Number" as CaseID,
// num(Right("Work_Log_ID",12)) as Work_Log_ID,
// "Work_Log_Submit_Date",
// "Work_Log_Submitter",
// "Work_Log_Type";
//
// SQL SELECT "Assign_WorkLog_Flag",
// Description,
// "Detailed_Description",
// "Incident_Number",
// "Work_Log_ID",
// "Work_Log_Submit_Date",
// "Work_Log_Submitter",
// "Work_Log_Type"
// FROM "HPD_WorkLog"
// Where (Work_Log_ID>'$(vMaxWorklogID)');

// UNQUALIFY *;
// WORKLOG:
// Concatenate LOAD *
// From (qvd)
// where not Exists('Work_Log_ID');
// STORE WORKLOG into Worklog.qvd(qvd);

[#9]

ok found out if i use

peek('Work_Log_ID', 0) that it shows correct value

[#10]

next part debugged and here the strange part:

max is ok now but it stills load all records instead of those above max.

if i do a preview of the table then i see that work_Log_ID is correctly formatted .

is there somewhere a way to see a trace of each compare he does in a load script?