topic Re: Load dates greater than - "where" query in Connectivity & Data Prep

Load dates greater than - "where" query

Sebastian_Dec — Fri, 21 Apr 2023 11:05:06 GMT

Hi, I'm connecting to a data warehouse and I want to limit date to e.g. a specific year or a few days back.
What might the "where" query look like?

Data format from warehouse is 'DD-MM-YYYY'

My code dont work 😞

LIB CONNECT TO 'HD (kp)';

Let vEndDate = text(date((Today()-7), 'DD-MM-YYYY'));

[SALES]:
LOAD
hid_shop,
date(hid_data) as data,
kod_gencode,
hid_seg;

SELECT
"hid_shop",
"hid_data",
"kod_gencode",
"hid_seg"
FROM "public"."SALES"
Where "hid_data" >= '$(vEndDate)'
;

Re: Load dates greater than - "where" query

BrunPierre — Fri, 21 Apr 2023 12:15:26 GMT

Try without the textual representation.

Let vEndDate = Date(Today()-7, 'DD-MM-YYYY');

Re: Load dates greater than - "where" query

Sebastian_Dec — Fri, 21 Apr 2023 12:38:07 GMT

I have error:

code:

LIB CONNECT TO 'HD (kp)'; Let vEndDate = Date(Today()-7, 'DD-MM-YYYY'); [obroty_72]: LOAD hid_sklep, date(hid_data) as data, kod_ean, hid_segment; SELECT "hid_sklep", "hid_data", "kod_ean", "hid_segment" FROM "public"."obroty_72" Where "hid_data" >= '$(vEndDate)' ;

error:

Started loading data Connected The following error occurred: Field 'hid_sklep' not found The error occurred here: SELECT "hid_sklep", "hid_data", "kod_ean", "hid_segment" FROM "public"."obroty_72" Where "hid_data" >= '14-04-2023' Data has not been loaded. Please correct the error and try loading again.

Re: Load dates greater than - "where" query

BrunPierre — Fri, 21 Apr 2023 12:44:18 GMT

Try referring to the fields in uppercase.

Alternatively, you can troubleshoot by just executing the SQL part (without the preceding load) and examining table values from the table viewer.