topic Re: invalid month in QlikView

invalid month

smilingjohn — Wed, 25 Nov 2020 16:16:04 GMT

Hi All

iam trying to implement the incremental load .

below is the script for that and the database is oracle.

after executing this is giving the error as shown in the screenshot .

Incremental:

LOAD

"Dept",

"RESOLVE_DATE",

"CLOSE_DATE",

LOAD_DATE,

"JIRA_PROJECT_KEY_ID";

From [..\dept.qvd(qvd)];

Last_Updated_Date:

LOAD

Date(max(LOAD_DATE,'MM-dd-YYYY')) as maxdate

Resident Incremental

LET vLast_Updated_Date = Peek('maxdate',0,'Last_Updated_Date');

concatenate

LOAD

"Dept",

"RESOLVE_DATE",

"CLOSE_DATE",

Date("LOAD_DATE") as LOAD_DATE,

"JIRA_PROJECT_KEY_ID";

SQL SELECT *

FROM "IPMO_SO".JIRA

where "LOAD_DATE" > '$(vLast_Updated_Date)';

Please need help on this

thanks in advance

Re: invalid month

sunny_talwar — Wed, 25 Apr 2018 15:20:25 GMT

Try this

Last_Updated_Date:

LOAD

Date(max(LOAD_DATE,'DD-MMM-YYYY')) as maxdate

Resident Incremental

LET vLast_Updated_Date = Peek('maxdate',0,'Last_Updated_Date');

Re: invalid month

Qrishna — Wed, 25 Apr 2018 15:22:39 GMT

Hi - Try this

your maxdate is in 'MM-dd-YYYY format but LOAD_DATE is in MM/ddYYYY.

change the format accordingly and make sure LOAD_DATE & vLast_Updated_Date are in the same format

updated script -->

Last_Updated_Date:

LOAD

Date(max(LOAD_DATE,'MM/dd/YYYY')) as maxdate

Resident Incremental

LET vLast_Updated_Date = Peek('maxdate',0,'Last_Updated_Date');

Re: invalid month

sunny_talwar — Wed, 25 Apr 2018 15:24:16 GMT

Actually this

Last_Updated_Date:

LOAD

Date(max(LOAD_DATE),'DD-MMM-YYYY') as maxdate

Resident Incremental

LET vLast_Updated_Date = Peek('maxdate',0,'Last_Updated_Date');

Re: invalid month

smilingjohn — Wed, 25 Apr 2018 15:47:55 GMT

Hi Sunny ,

Thanks for the reply ..

With the above logic it is loading double records ... and my load date format within the QVD file MM-dd-yyyy(4/24/2018)

when i run the script with your logic it is loading double records ( daily we do load 30000 records) now it is loading 60000 record

do i need to make any change ?

Re: invalid month

sunny_talwar — Wed, 25 Apr 2018 15:51:08 GMT

When you use this, the record count doubles, really??

Incremental:

LOAD

"Dept",

"RESOLVE_DATE",

"CLOSE_DATE",

LOAD_DATE,

"JIRA_PROJECT_KEY_ID";

From [..\dept.qvd(qvd)];

Last_Updated_Date:

LOAD

Date(max(LOAD_DATE),'DD-MMM-YYYY') as maxdate

Resident Incremental

LET vLast_Updated_Date = Peek('maxdate',0,'Last_Updated_Date');

concatenate

LOAD

"Dept",

"RESOLVE_DATE",

"CLOSE_DATE",

Date("LOAD_DATE") as LOAD_DATE,

"JIRA_PROJECT_KEY_ID";

SQL SELECT *

FROM "IPMO_SO".JIRA

where "LOAD_DATE" > '$(vLast_Updated_Date)';

Re: invalid month

smilingjohn — Wed, 25 Apr 2018 15:56:19 GMT

I mean in the reloading script first it fetches the qvd which is 30000 then it shows

last updtaed date 1 line fetched

and third line it should show 10000 records but actaully it is showing 40000 records which is wrong ..

i hope u understand what i am saying ...seeing this i have to kill the process

Re: invalid month

sunny_talwar — Wed, 25 Apr 2018 16:05:14 GMT

How does your variable look like? 24-Apr-2018 or something else?

Re: invalid month

smilingjohn — Wed, 25 Apr 2018 16:12:30 GMT

Hi Sunny ,

No sunny i just took the screen shot from the debug while still the process was runing

and the values within vLast_Updated_Date is like this

But it is keeping on loading the records ...

live data is like till 24 aprill my data is around 50 lakhs rows and daily it will be around 20 thousand rows insertion ..

but this is keeping on loading ...

there is something wrong

Re: invalid month

sunny_talwar — Wed, 25 Apr 2018 16:14:35 GMT

Try this

Incremental:

LOAD

"Dept",

"RESOLVE_DATE",

"CLOSE_DATE",

LOAD_DATE,

"JIRA_PROJECT_KEY_ID";

From [..\dept.qvd(qvd)];

Last_Updated_Date:

LOAD

Date(max(LOAD_DATE),'MM/DD/YYYY') as maxdate

Resident Incremental

LET vLast_Updated_Date = Peek('maxdate',0,'Last_Updated_Date');

concatenate

LOAD

"Dept",

"RESOLVE_DATE",

"CLOSE_DATE",

Date("LOAD_DATE") as LOAD_DATE,

"JIRA_PROJECT_KEY_ID";

SQL SELECT *

FROM "IPMO_SO".JIRA

where "LOAD_DATE" > TO_DATE('$(vLast_Updated_Date)', 'mm/dd/yyyy');

Re: invalid month

sunny_talwar — Wed, 25 Apr 2018 16:19:32 GMT

Have you fixed the parenthesis?

Re: invalid month

smilingjohn — Wed, 25 Apr 2018 16:24:00 GMT

Hi Sunny

Thanks Legend this works