topic load sql select where part not functioning in QlikView

load sql select where part not functioning

Fri, 20 Dec 2013 13:00:28 GMT

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

Re: load sql select where part not functioning

Colin-Albert — Fri, 20 Dec 2013 13:05:38 GMT

Use PEEK to get vMaxWorklogID

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

Re: load sql select where part not functioning

Anonymous — Fri, 20 Dec 2013 13:10:02 GMT

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

Re: load sql select where part not functioning

Colin-Albert — Fri, 20 Dec 2013 13:10:16 GMT

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.

Re: load sql select where part not functioning

Fri, 20 Dec 2013 13:14:05 GMT

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

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

Re: load sql select where part not functioning

Fri, 20 Dec 2013 13:22:07 GMT

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

Re: load sql select where part not functioning

Anonymous — Fri, 20 Dec 2013 13:55:32 GMT

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.

Re: load sql select where part not functioning

Fri, 20 Dec 2013 14:00:43 GMT

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

Re: load sql select where part not functioning

Fri, 20 Dec 2013 14:04:32 GMT

ok found out if i use

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

Re: load sql select where part not functioning

Fri, 20 Dec 2013 14:14:16 GMT

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?

Re: load sql select where part not functioning

Anonymous — Fri, 20 Dec 2013 14:16:40 GMT

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

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

Re: load sql select where part not functioning

Fri, 20 Dec 2013 14:47:33 GMT

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

Re: load sql select where part not functioning

Anonymous — Fri, 20 Dec 2013 14:57:25 GMT

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

Re: load sql select where part not functioning

Fri, 20 Dec 2013 15:06:42 GMT

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

Re: load sql select where part not functioning

Fri, 20 Dec 2013 15:09:06 GMT

thanks for the solution of the first part

Re: load sql select where part not functioning

Anonymous — Fri, 20 Dec 2013 15:09:40 GMT

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

Re: load sql select where part not functioning

Fri, 20 Dec 2013 15:13:39 GMT

Re: load sql select where part not functioning

Anonymous — Fri, 20 Dec 2013 15:16:33 GMT

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

Re: load sql select where part not functioning

Fri, 20 Dec 2013 15:26:58 GMT

Re: load sql select where part not functioning

Fri, 20 Dec 2013 15:27:40 GMT