Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

load sql select where part not functioning

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

21 Replies
Colin-Albert

Use PEEK to get vMaxWorklogID

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

Anonymous
Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

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
Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

ok found out if i use

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

Not applicable
Author

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?