Qlik Community

Ask a Question

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a $200 Amazon Gift Card! Watch Video
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator II
Creator II

Filtering script by date

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 messageerror message

1 Solution

Accepted Solutions
Specialist III
Specialist III

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

 

View solution in original post

4 Replies
Specialist III
Specialist III

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

Creator II
Creator II

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

Specialist III
Specialist III

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

 

View solution in original post

Creator II
Creator II

great!!! thank's for your help