topic Use between operatore in qlikview back end in QlikView

Use between operatore in qlikview back end

gireesh1216 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Team,

I have a data/Query like this:

1)Select Start_Date,End_Date from Employee.------->This data coming from A Database.

2)Select * from source where CREATE_DT BETWEEN Start_Date AND End_Date;------>This data coming from B Database.

Note: Start_Date AND End_Date in query 2 coming from A Databse.

This data coming from A Database:
Table1:
Start_Date,End_Date
01-01-2016,10-01-2016
15-01-2016,25-01-2016

This data coming from B Database:

table2:
CREATE_DT
01-01-2016
10-01-2016
15-01-2016
25-01-2016
17-01-2016 - this data is between 15-01-2016 to 25-01-2016
11-01-2016 -These dates not there in range of start date and end date of table 1
14-01-2016 --These dates not there in range of start date and end date of table 1

Looking OUTPUT like below:

CREATE_DT
01-01-2016
10-01-2016
15-01-2016
25-01-2016
17-01-2016

query : Where CREATE_DT >= '$(vStart_Date)' AND CREATE_DT <= '$(vEnd_Date)'; will get all data from 01-01-2016 to 25-01-2016.

I don't want 11-01-2016 and 14-01-2016 in my output.

so the above query not working for my requirmnet.

Please help me

Thanks

Gireesh

Re: Use between operatore in qlikview back end

techvarun — Tue, 30 Jan 2018 07:55:48 GMT

unfortunately qlikView doesn't have any between function.

Use something like:

Load * from Table Where Start_Date >= '201701' and End_Date <= '201712';

Re: Use between operatore in qlikview back end

rahulpawarb — Tue, 30 Jan 2018 08:03:49 GMT

Hello Gireeesh,

Please refer below sample script (Concidering that Employee table will have only one row):

//Sample Script

Employee:
LOAD * INLINE [
Start_Date, End_Date
01/01/2017, 12/31/2017
];


LET vStart_Date = Peek('Start_Date', 0, 'Employee');
LET vEnd_Date = Peek('End_Date', 0, 'Employee');
source:
LOAD *
Where CREATE_DT >= '$(vStart_Date)' AND CREATE_DT <= '$(vEnd_Date)';
LOAD * INLINE [
TRAN_ID, CREATE_DT
TRN201701, 01/01/2017
TRN201702, 02/01/2017
TRN201703, 03/01/2017
TRN201704, 04/01/2017
TRN201801, 01/01/2018
TRN201802, 02/01/2018
];

Hope this will be helpful.

Thank you!

Rahul Pawar

Re: Use between operatore in qlikview back end

gireesh1216 — Tue, 30 Jan 2018 09:18:07 GMT

good.

But I want to use between operator in qlikivew.

Where CREATE_DT between '$(vStart_Date)' AND '$(vEnd_Date)';

Re: Use between operatore in qlikview back end

techvarun — Tue, 30 Jan 2018 09:24:26 GMT

You cannot use between because it doesn't exists in Qlik.

You can create a view in SQL by using between function and use that view directly in Qlik.

Re: Use between operatore in qlikview back end

gireesh1216 — Tue, 30 Jan 2018 09:26:08 GMT

Thanks for ur replay

I will share some sample data. Please work on that.