topic Re: Filtering script by date in App Development

Filtering script by date

ecabanas — Mon, 25 Feb 2019 11:28:25 GMT

Hi,

I would like to load certain amount of data from our Netsuite data base, but I could not reach it, any idea what I'm doing wrong?

Thanks

NSTransactions:
LOAD 
CREATED_FROM_ID,
TRANDATE,
year(TRANDATE) as year_trandate,
TRANSACTION_EXTID,
TRANSACTION_ID,
TRANSACTION_TYPE,
ENTITY_ID,
If(Left([TRANSACTION_EXTID], 3)='FV0',
Replace([TRANSACTION_EXTID], 'FV', 'PV'),
Replace([TRANSACTION_EXTID], 'FV', 'PR')) as SalesId,
XBASEAMT1+XBASEAMT2+XBASEAMT3+XBASEAMT4 as Bruto_Fra;
SQL SELECT 
CREATED_FROM_ID,
TRANDATE,
TRANSACTION_EXTID,
TRANSACTION_ID,
TRANSACTION_TYPE,
ENTITY_ID,
XBASEAMT1,XBASEAMT2,XBASEAMT3,XBASEAMT4
FROM "ULABOX, S_L_".Administrator.TRANSACTIONS
Where 
Date(Floor(TRANDATE)) >= MakeDate(2017,01,01) and Date(Floor(TRANDATE)) <= MakeDate(2017,12,31);

error message

Re: Filtering script by date

marcus_malinow — Mon, 25 Feb 2019 11:43:27 GMT

hi,

the issue here is that your WHERE clause, which is being sent to SQL, is using Qlik syntax

Where
Date(Floor(TRANDATE)) >= MakeDate(2017,01,01) and Date(Floor(TRANDATE)) <= MakeDate(2017,12,31);

Change this to

Where
TRANDATE >= '2017-01-01' and TRANDATE <= '2017-12-31';

Re: Filtering script by date

ecabanas — Mon, 25 Feb 2019 12:03:58 GMT

Hi @marcus_malinow

Thank's for your help, now it works, but just one more question, how could I make these dates dynamically? for example, I want to load current month and store previous month in a QVD, fully auto depending on the date, if it's day number 10 of the month, store the previous and only oad the current one...any idea how to do that?

Thank's again

Re: Filtering script by date

marcus_malinow — Mon, 25 Feb 2019 12:12:21 GMT

Hi,

you can pass in your dates via variables

e.g.

before your LOAD:

LET vStartDate = date([whatever your expression is to determine start date], 'YYYY-MM-DD');

LET vEndDate = date([whatever your expression is to determine end date], 'YYYY-MM-DD');

in your WHERE clause:

Where
TRANDATE >= '$(vStartDate)' and TRANDATE <= '$(vEndDate)';

Re: Filtering script by date

ecabanas — Mon, 25 Feb 2019 12:18:42 GMT

great!!! thank's for your help