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

[Anonymous]

Try you it

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 = Peek('Work_Log_ID',0);

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

[#3]

max value is ok with script you posted

but it still loads all records from HPD_Worklog instead of only those bigger than max value

[Anonymous]

Try you it

LOAD
max(num("Work_Log_ID")) as Work_Log_ID // not right
From (qvd);
// group by "Work_Log_ID";
// LET vMaxWorklogID = peek("Work_Log_ID", 0, WORKLOG);
let vMaxWorklogID = Peek('Work_Log_ID',0);

.....

.....

....

PD: you can add a value field Work_Log_ID

[#5]

script you posted works to find max.

Now i will have to find a solution to second part where the "where" clause does not work

[#6]

thanks for the solution of the first part

[Anonymous]

You can attach an example of the values ​​that exist in the field Work_Log_ID

[#8]

[Anonymous]

Try you it

WORKLOG:

LOAD
max(num("Work_Log_ID")) as Work_Log_ID
From (qvd);
let vMaxWorklogID = num(Peek('Work_Log_ID',0));

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) ); // not quote

[#10]

[#11]