topic Re: QVD Loader in QlikView

QVD Loader

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

Hello all,

I need your help on a loader. I am loading data onto a QVD and when I put a date criteria on there it seems to fail. Any pointers would be a great help.

Many thanks in advance

AGREEMENT:

SQL

SELECT

ID,

SOURCESYSTEMID,

AGREEMENTNUMBER,

"EFF_STA_DTE",

"EFF_END_DTE",

CURRENTLPIRATE

FROM E_AFPG_BASE.AGREEMENT

where EFF_END_DTE >='31/12/3499';

//where Date(Floor(EFF_END_DTE)) >='31/12/3499';

STORE AGREEMENT INTO (QVD);

drop table AGREEMENT;

Re: QVD Loader

YoussefBelloum — Wed, 22 Nov 2017 14:53:05 GMT

Hi,

AGREEMENT:

SQL

SELECT

ID,

SOURCESYSTEMID,

AGREEMENTNUMBER,

"EFF_STA_DTE",

"EFF_END_DTE",

CURRENTLPIRATE

FROM E_AFPG_BASE.AGREEMENT

where EFF_END_DTE >='31/12/3499';

//where Date(Floor(EFF_END_DTE)) >='31/12/3499';

STORE AGREEMENT INTO 'C:\Users\h04818318\Desktop\QlikView\ALFA\AGREEMENT.QVD' (QVD);

drop table AGREEMENT;

Re: QVD Loader

Mark_Little — Wed, 22 Nov 2017 14:57:48 GMT

Hi,

What error do you receive?

I would make sure the date format match frist

So something like

DATE(EFF_END_DTE,'DD/MM/YYYY') >= '31/12/3499' //Also do you mean a year of 3499?

If that doesn't work it maybe that the field isn't seen as Date so maynbe also need to look at DATE#() function.

DATE(DATE#(EFF_END_DTE,'DD/MM/YYYY'),'DD/MM/YYYY') >= '31/12/3499'

Basically the DATE#() changes a string to a date, you need to make sure the supplied format in the function matches the format in the field.

Mark

Re: QVD Loader

OmarBenSalem — Wed, 22 Nov 2017 15:14:29 GMT

Can you please share a screen shot on the error message?

Re: QVD Loader

mohiahmed — Wed, 22 Nov 2017 15:36:26 GMT

Thanks for the help guys, but still not working. Getting the below error message

SQL##f - SqlState: 37000, ErrorCode: 4294963590, ErrorMsg: [Teradata][ODBC Teradata Driver][Teradata Database] Syntax error: Data Type "DATE#" does not match a Defined Type name.

AGREEMENT:

SQL

SELECT

ID,

SOURCESYSTEMID,

AGREEMENTNUMBER,

"EFF_STA_DTE",

"EFF_END_DTE",

CURRENTLPIRATE

FROM E_AFPG_BASE.AGREEMENT

Where DATE(DATE#(EFF_END_DTE,'DD/MM/YYYY'),'DD/MM/YYYY') >= '31/12/3499'

STORE AGREEMENT INTO 'C:\Users\h04818318\Desktop\QlikView\ALFA\AGREEMENT.QVD' (QVD)

Re: QVD Loader

OmarBenSalem — Wed, 22 Nov 2017 15:41:11 GMT

That's normal since you're doing it in the select part in which you should use the language of your database, in this case teradata; please do as follow:

AGREEMENT:

load *

Where DATE(DATE#(EFF_END_DTE,'DD/MM/YYYY'),'DD/MM/YYYY') >= '31/12/3499';

SQL

SELECT

ID,

SOURCESYSTEMID,

AGREEMENTNUMBER,

"EFF_STA_DTE",

"EFF_END_DTE",

CURRENTLPIRATE

FROM E_AFPG_BASE.AGREEMENT

;

STORE AGREEMENT INTO 'C:\Users\h04818318\Desktop\QlikView\ALFA\AGREEMENT.QVD' (QVD)

Re: QVD Loader

mohiahmed — Wed, 22 Nov 2017 15:41:40 GMT

Thanks mate, still getting an error

SQL##f - SqlState: 22003, ErrorCode: 4294963761, ErrorMsg: [Teradata][ODBC Teradata Driver][Teradata Database] A character string failed conversion to a numeric value.

AGREEMENT:

SQL

SELECT

ID,

SOURCESYSTEMID,

AGREEMENTNUMBER,

"EFF_STA_DTE",

"EFF_END_DTE",

CURRENTLPIRATE

FROM E_AFPG_BASE.AGREEMENT

where EFF_END_DTE >='31/12/3499'

Re: QVD Loader

mohiahmed — Wed, 22 Nov 2017 15:49:52 GMT

Thanks mate, that is working well. Downloading the data now and not failing over.

Once again, many thanks

Re: QVD Loader

mohiahmed — Thu, 23 Nov 2017 11:22:16 GMT

Hi Omar, I need your help again if possible please.

When I was running the above query, it was running and then didn't bring any data. I looked up at Teradata and noticed that date was backwards and tried that too and still noting coming up.

Teradata query I am using is to get the data.

Select * from E_AFPG_BASE.

AGREEMENT

Where EFF_END_DTE>= cast( '3499/12/31' as date)

For loader I have tried Where DATE(DATE#(EFF_END_DTE,'YYYY/MM/DD'),'YYYY/MM/DD') >= '3499/12/31';

Will I be able to use Cast and possibly date format is not recognised somehow?

Re: QVD Loader

OmarBenSalem — Thu, 23 Nov 2017 13:05:18 GMT

what if you do as follow:

AGREEMENT :

SQL

SELECT

ID,

SOURCESYSTEMID,

AGREEMENTNUMBER,

"EFF_STA_DTE",

"EFF_END_DTE",

CURRENTLPIRATE

FROM E_AFPG_BASE.AGREEMENT

Where EFF_END_DTE>= cast( '3499/12/31' as date)

;

STORE AGREEMENT INTO 'C:\Users\h04818318\Desktop\QlikView\ALFA\AGREEMENT.QVD' (QVD)

Re: QVD Loader

mohiahmed — Thu, 23 Nov 2017 14:15:19 GMT

Thanks Omar, that was perfect.