topic Re: Column not found error while fetching data from MySQL in QlikView

Column not found error while fetching data from MySQL

kishorj1982 — Tue, 17 Jul 2018 03:00:36 GMT

Hi All,

Below is my incremental load script,

I am using date field for comparion.Although filed is present in DB it is throwing error as

AddressMainLoadDate column is missing.

set vLastReloadTime='2018-11-05 08:06:52 +00:00';

SET QVDFILE = $(QvdPath)DimCustomerAddress.qvd;

SET PKexp = CustomerId;

SET DailyQVD = $(DailyQvd)DimCustomerAddress&'_'&$(vDate).qvd;

SET QVD_HEADER = DimCustomerAddress;

SET TEMP_HEADER = DimCustomerAddress_TEMP;

//to_timestamp(LAST_UPDATE_DATE,'YYYY-MM-DD HH24:MI:SS')>=;

IF FileSize('$(QVDFILE)') > 0 THEN

$(QVD_HEADER):

SQL SELECT *

FROM CDPVault.BDV.DimCustomer

where AddressMainLoadDate >= '$(vLastReloadTime)' ;

store $(QVD_HEADER) INTO $(DailyQVD);

$(TEMP_HEADER):

LOAD DISTINCT *,

$(PKexp) AS %PK

RESIDENT $(QVD_HEADER);

//STORE $(TEMP_HEADER) INTO $(TEMP_HEADER); THIS CODE ADDED WHEN NEED TO TEST THE

//DELTA RECORDS ONLY MEANS EXACTLY HOW MANY RECORDS ARE GETTING POPULARED

CONCATENATE('$(TEMP_HEADER)')

LOAD *

FROM $(QVDFILE) (Qvd) WHERE NOT EXISTS(%PK,CustomerId); ///////THIS PORTION NEED TO BE EDITED EVERY TIME

DROP FIELD %PK;

STORE $(TEMP_HEADER) INTO $(QVDFILE);

DROP TABLE $(TEMP_HEADER);

ELSE

$(QVD_HEADER):

SQL SELECT *

FROM CDPVault.BDV.DimCustomer;

STORE $(QVD_HEADER) INTO $(QVDFILE);

exit script;

Could you please suggest on this.

Thanks

Re: Column not found error while fetching data from MySQL

tamilarasu — Tue, 17 Jul 2018 03:46:00 GMT

Hi Kishor,

Try loading below script and check whether the field is available or not. If it is available check the field name is matching properly (case sensitive).

TEST:

SQL SELECT *

FROM CDPVault.BDV.DimCustomer;

Re: Column not found error while fetching data from MySQL

petter — Tue, 17 Jul 2018 04:44:25 GMT

Have you checked the MySQL database to see if the column really is there? AddressMainLoadDate

Re: Column not found error while fetching data from MySQL

kishorj1982 — Tue, 17 Jul 2018 06:39:18 GMT

Yes,

Column is already there and I have already created sample QVD for this.

Thanks

Re: Column not found error while fetching data from MySQL

kishorj1982 — Tue, 17 Jul 2018 06:57:12 GMT

My Original AddressMainLoadDate format is 2018-06-08 09:04:52 +00:00

May I know how should I format other dates in same format.

Thanks

Re: Column not found error while fetching data from MySQL

annapochyla — Tue, 17 Jul 2018 08:24:59 GMT

Hi,

Try using date format without the last part with seconds fraction.

set vLastReloadTime='2018-11-05 08:06:52';

Anna

Re: Column not found error while fetching data from MySQL

kishorj1982 — Tue, 17 Jul 2018 08:36:40 GMT

Hi,

Thanks for the response,

May I know how should I show the column in 2018-06-08 09:04:52 +00:00 this format.

What changes do I need to do in Date(column,'YYYY-MM-DD hh:mm:dd') in this expression.

Because I am not getting how to move ahead.

Thanks