Filtering script by date

[ecabanas]

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

[marcus_malinow]

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

[ecabanas]

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

[marcus_malinow · Accepted solution]

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

[ecabanas]

great!!! thank's for your help