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: 
smilingjohn
Specialist
Specialist

invalid month

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 .

oracleinvalidmnt.PNG

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

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

11 Replies
sunny_talwar

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

krishna_2644
Specialist III
Specialist III

Hi - Try this

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

change the format accordingly and make surLOAD_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');

sunny_talwar

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

smilingjohn
Specialist
Specialist
Author

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 ?

sunny_talwar

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

smilingjohn
Specialist
Specialist
Author

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

sunny_talwar

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

smilingjohn
Specialist
Specialist
Author

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

snow.PNG

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

sunny_talwar

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